Error Handling in VLOOKUP with VBA

[sahaider]

Hello ,
I have the following VBA code that includes VLOOKUP.

I am trying to include error trapping in the code so that when Vlookup returns #N/A write the code writes "Missing" in those cells.

lastRow = Worksheets("LTE_Best Server by RSRP").Cells.SpecialCells(xlCellTypeLastCell).Row
With Worksheets("LTE_Best Server by RSRP")
For r1 = 2 To lastRow

.Range("G" & r1) = Application.WorksheetFunction.IfNa(VLookup(CStr(.Range("F", r1)), Worksheets("ALL 4G Hierarchy").Range("E2:J125000"), 5, False), "Missing")

Next

End With

But i am getting Sub/Function not defined for VLOOKUP when I run the code.

Can't figure out what is wrong in the code.

 

[mart37]

do you mean ISNA()

 

[Peter_SSs]

I think that you need a couple of changes. See how this goes

.Range("G" & r1) = Application.WorksheetFunction.IfNa(WorksheetFunction.VLookup(CStr(.Range("F" & r1)), Worksheets("ALL 4G Hierarchy").Range("E2:J125000"), 5, False), "Missing")

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mart37]

Maybe you can use something like this:

Dim V As Variant
V = Application.VLookup(.........)
If Application.IsNA(V) Then
    Debug.Print "failure"
Else
    Debug.Print "success"
End If

 

[mart37]

.Range("G" & r1) = Application.WorksheetFunction.IfNa(Application.VLookup(CStr(.Range("F" & r1)), Worksheets("ALL 4G Hierarchy").Range("E2:J125000"), 5, False), "Missing")

 

[sahaider]

I think that you need a couple of changes. See how this goes

.Range("G" & r1) = Application.WorksheetFunction.IfNa(WorksheetFunction.VLookup(CStr(.Range("F" & r1)), Worksheets("ALL 4G Hierarchy").Range("E2:J125000"), 5, False), "Missing")

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

hello , just tried as suggested, no success i'm afraid.

 

[Fluff]

How about

Dim Vl As Variant
LastRow = Worksheets("LTE_Best Server by RSRP").Cells.SpecialCells(xlCellTypeLastCell).Row
With Worksheets("LTE_Best Server by RSRP")
   For r1 = 2 To LastRow
      
      Vl = Application.VLookup(CStr(.Range("F", r1)), Worksheets("ALL 4G Hierarchy").Range("E2:J125000"), 5, False)
      If IsError(Vl) Then
         .Range("G" & r1) = "Missing"
      Else
         .Range("G" & r1) = Vl
      End If
   Next

End With

 

[Peter_SSs]

just tried as suggested, no success i'm afraid.

That gives us nothing to go on.

In what way did it fail?
eg
An error message? (What was it?)
Wrong values returned?
Nothing?
Crashed excel?
etc


Surely that notation would error?

Vl = Application.VLookup(CStr(.Range("F", r1)), Worksheets("ALL 4G Hierarchy").Range("E2:J125000"), 5, False)

 

[Fluff]

Surely that notation would error?

You're quite right it will, didn't notice that, it should be

Dim Vl As Variant
LastRow = Worksheets("LTE_Best Server by RSRP").Cells.SpecialCells(xlCellTypeLastCell).Row
With Worksheets("LTE_Best Server by RSRP")
   For r1 = 2 To LastRow
      
      Vl = Application.VLookup(CStr(.Range("F" & r1)), Worksheets("ALL 4G Hierarchy").Range("E2:J125000"), 5, False)
      If IsError(Vl) Then
         .Range("G" & r1) = "Missing"
      Else
         .Range("G" & r1) = Vl
      End If
   Next

End With

 

[sahaider]

Vl = Application.VLookup(CStr(.Range("F", r1)), Worksheets("ALL 4G Hierarchy").Range("E2:J125000"), 5, False)

hello ,

Thanks for code.
Getting Error 1004 on this line.