Philip1957
Board Regular
- Joined
- Sep 30, 2014
- Messages
- 182
- Office Version
- 365
- Platform
- Windows
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.
Thanks in advance. Any assistance is greatly appreciated.
Happy Friday.
~ Phil
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