Stuck on a Type Mismatch Error

[Philip1957]

Greetings,

I'm back for help & education once again.

I have a table in a workbook (IP Lookup.xlsx) with 2 columns. The second column is data from a Vlookup. It has numbers (IP Addresses) and errors (#N/A) formatted as "General". I want to copy the Vlookup results, paste them as values, and delete the rows that have #N/A.

The copy & paste works fine. The delete rows will run through once, and I can see i decrement in the Locals window. I get the type mismatch the second time it tries to execute the IF statement and I can't figure out why. Here's my code.

Private Sub Clean_SaveCSV()

Dim wb1 As Workbook
    Set wb1 = ThisWorkbook
Dim wb3 As Workbook
    Set wb3 = Workbooks("IP Lookup.xlsx")
Dim wb4 As Workbook
    Set wb4 = Workbooks("IP_Master.xlsx")
Dim lrow As Integer
Dim i As Integer

Application.ScreenUpdating = False

    With Workbooks("IP Lookup.xlsx").Worksheets("Sheet1")
        lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
  
    wb3.Activate
    Range("B:B").Select
    Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    For i = lrow To 1 Step -1
        If (Cells(i, "B").Value) = "#N/A" Then
            Cells(i, "B").EntireRow.Delete
        End If
    Next i
    
Application.ScreenUpdating = True
    
End Sub

Thanks in advance. Any assistance is greatly appreciated.
Happy Friday.
~ Phil

 

[ranman256]

Cells(i, "B").Value) = "#N/A",
N/A is not a string. Is an error. use IsNA(cell)

 

[Fluff]

Rather than looping you can use

With Range("B1", Range("B" & Rows.Count).End(xlUp))
   .Value = .Value
   .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With

 

[Philip1957]

Cells(i, "B").Value) = "#N/A",
N/A is not a string. Is an error. use IsNA(cell)

Thanks. I thought since I had converted the VBA errors to text Excel would see "#N/A" as just another string.

I appreciate you taking the time to assist & educate me.

~ Phil

 

[Philip1957]

Rather than looping you can use

With Range("B1", Range("B" & Rows.Count).End(xlUp))
   .Value = .Value
   .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With

Thank you once again.

I suspected part of the problem was the "#N/A", but as I explained to ranman256 I thought it would be seen as a string since I converted it to text.

I usually have trouble getting VBA to loop properly. It figures the one time I got it right, I didn't need to.

~ Phil

 

[Fluff]

You're welcome & thanks for the feedback.