CODEWITHSUNDEEP
vbaexcel
blau
blau

Reputation: 55

VBA runtime error 13 Type Mismatch If statment

I receive Runtime Error '13': Type Mismatch when I try to run the code. Debug highlights the 'IF' statements, but I can't figure out where the mistake is. Any help would be appreciated. Thanks 

  Dim i As Integer
  Dim lastRow As Long
  Workbooks("Template Part_II.xlsx").Worksheets(2).Activate
 lastRow = Cells(Rows.Count, 1).End(xlUp).Row
 For i = 2 To lastRow
     If Cells(i, 1).Value <> "#N/A" And Cells(i, 1).Value <> "00000000-000" Then
        Cells(i, 1).Copy
        Worksheets(1).Range("A2:A" & lastRow).PasteSpecial xlPasteValues
     End If
 Next I

and in fact I'm trying to do this:
I have one Sheet where I have 100 Rows of various IDs and I want to copy this IDs to another sheet without possible non ID strings in this case it can be #N/A or 00000000-0000, also I don't want those non copied cells to appear as blanks in destination range.

Upvotes: 0

Views: 587

Answers (1)

DAXaholic
DAXaholic

Reputation: 35338

Wrap your accesses to the cell inside a check which ensures the cell contains no error value (e.g. a cell 'containing' a division by 0) like so 

 ...
 For i = 2 To lastRow
     If Not IsError(Cells(i, 1).Value) Then
        If Cells(i, 1).Value <> "#N/A" And Cells(i, 1).Value <> "00000000-000" Then
           Cells(i, 1).Copy
           Worksheets(1).Range("A2:A" & lastRow).PasteSpecial xlPasteValues
        End If
     End If
 Next i
 ...

Note: I tried to insert the condition at the front of the existing If but it seems VBA does not use short-circuiting therefore the wrapping

Update due to comment

You may want to change your code like this 

Dim i As Integer
Dim lastRow As Long
Dim targetRow As Long

Workbooks("Template Part_II.xlsx").Worksheets(2).Activate
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
targetRow = 2
For i = 2 To lastRow
   If Not IsError(Cells(i, 1).Value) Then
       If Cells(i, 1).Value <> "#N/A" And Cells(i, 1).Value <> "00000000-000" Then
          Cells(i, 1).Copy
          Worksheets(1).Cells(targetRow, 1).PasteSpecial xlPasteValues
          targetRow = targetRow + 1
       End If
   End If
Next i

Upvotes: 2

Related Questions