PDA

View Full Version : Run Time Error 13 (Type Missmatch)



karmegam
02-08-2017, 11:23 PM
Dear Friends, I have done a macro for reports clean. Please see below my code. I am referring 8th column here and the variable val1&val has been declared as variant wherever i referring this getting error. But yesterday it was working good. Please help to resolve this ( Error is when only referring 8th column)


With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


For i = lr To 2 Step -1
val = Trim(Cells(i, 1).Value)
val1 = Trim(Cells(i, 8).Value)
If val = "" And val1 = "" Then
Rows(i).Delete
End If
Next i

Workbooks("PO HEADERS.xlsm").Sheets("PO HEADERS").Activate
With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = lr To 2 Step -1
val = Trim(Cells(i, 8).Value)
le = Len(val)
val1 = Left(val, 1)
If val = "<" Then
Rows(i).Delete
End If
Next i

Workbooks("PO HEADERS.xlsm").Sheets("PO HEADERS").Activate
With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = lr To 2 Step -1
val = Trim(Cells(i, 8).Value)
val1 = Left(val, 3)
If val1 = "***" Then
Rows(i).Delete
End If
Next i

GTO
02-08-2017, 11:38 PM
Welcome to the forum:hi:,

Please use code tags like:


'Your code goes here

...and decent indentation when posting code. It makes it so much easier for anyone to read.

Reference your issue, I don't see a problem with the snippet shown. I would suggest posting at least the entire procedure.

Mark

karmegam
02-09-2017, 12:05 AM
Hello GTO Thanks for reply the function of my macro is cleaning raw data having cells values empty , < and ****. For example i have attached few columns values



<13/02 afternoon- See if inspection is needed on 13/02?








<


**Please use our DHL account - send us tracking number once ready**





<








<








<





<PO Regul








<








<


<PO Regul





dim val,val1 as variant

With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = lr To 2 Step -1
val = Trim(Cells(i, 1).Value)
val1 = Trim(Cells(i, 8).Value) ------------------> Referring 8th column value and getting type mismatch Error
If val = "" And val1 = "" Then
Rows(i).Delete
End If
Next i

Workbooks("PO HEADERS.xlsm").Sheets("PO HEADERS").Activate
With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = lr To 2 Step -1
val = Trim(Cells(i, 8).Value) ----------> Referring 8th column value and getting type mismatch Error
le = Len(val)
val1 = Left(val, 1)
If val = "<" Then
Rows(i).Delete
End If
Next i

Workbooks("PO HEADERS.xlsm").Sheets("PO HEADERS").Activate
With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = lr To 2 Step -1
val = Trim(Cells(i, 8).Value) -------------------->Referring 8th column value and getting type mismatch Error
val1 = Left(val, 3)
If val1 = "***" Then
Rows(i).Delete
End If
Next i

GTO
02-09-2017, 12:17 AM
Reference the code:

Please use code tags like:


'Your code goes here

...and decent indentation when posting code. It makes it so much easier for anyone to read.

You can click on the pound sign icon above the reply box to have the tags inserted. Post the VBA code between the tags.

As to the example "columns", it would be better to simply attach a small workbook replicating the data on Workbooks("PO HEADERS.xlsm").Sheets("PO HEADERS")

Click the Go Advanced button, then scroll down to Manage Attachments.

karmegam
02-09-2017, 12:27 AM
dim val,val1 as variant

With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = lr To 2 Step -1
val = Trim(Cells(i, 1).Value)
val1 = Trim(Cells(i, 8).Value) '------------------>Referring 8th column value and getting type mismatch
If val = "" And val1 = "" Then
Rows(i).Delete
End If
Next i

Workbooks("PO HEADERS.xlsm").Sheets("PO HEADERS").Activate[
With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = lr To 2 Step -1
val = Trim(Cells(i, 8).Value) ' ----------> Referring 8th column value and getting type mismatch'
val1 = Left(val, 1)
If val = "<" Then
Rows(i).Delete
End If
Next i

Workbooks("PO HEADERS.xlsm").Sheets("PO HEADERS").Activate
With ActiveSheet
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = lr To 2 Step -1
val = Trim(Cells(i, 8).Value) ' -------------------->Referring 8th column value and getting type mismatch '
val1 = Left(val, 3)
If val1 = "***" Then
Rows(i).Delete
End If
Next i

GTO
02-09-2017, 01:23 AM
Hi again karmegam,

Sorry we seem to be communicating poorly. I tried with the data you provided and cannot replicate the error. Please put together a small workbook with the data on "PO Headers" on one sheet, and the data on whatever the initial active sheet is on another worksheet. Include the entire procedure (the Sub or Function) as you have it at present. Upload the workbook as previously detailed.