Stuck on a Type Mismatch Error

Philip1957

Board Regular
Joined
Sep 30, 2014
Messages
149
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.

VBA 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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,974
Cells(i, "B").Value) = "#N/A",
N/A is not a string. Is an error. use IsNA(cell)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Rather than looping you can use
VBA Code:
With Range("B1", Range("B" & Rows.Count).End(xlUp))
   .Value = .Value
   .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
 
Solution

Philip1957

Board Regular
Joined
Sep 30, 2014
Messages
149
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

Board Regular
Joined
Sep 30, 2014
Messages
149
Rather than looping you can use
VBA Code:
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. :rolleyes:

~ Phil
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,388
Messages
5,636,032
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top