Dealing with #n/a in application.vlookup

[JonRowland]

Hi,

I am attempting to use Application.Vlookup rather than input my Vlookup formula within the cell. After a lot of reading I still don't understand how I can deal with #N/A.

So to previously deal with #N/A, I would have entered as a formula this ISERROR

=IF(ISERROR(VLOOKUP(C2,MyWorkBook!Sheet1!$A:$D,2,FALSE)),"ADD_TO_DB",VLOOKUP(C2,MyWorkBook!Sheet1!$A:$D,2,FALSE))

Thus #N/A would become ADD_TO_DB.

My question is what do I need to change/add to the following code to get the same result?

<CODE>
With objActiveWkB.Sheets("MyWorkBook")

For lngRw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row

'Site Location (1) & (2)
.Cells(lngRw, 2) = Application.VLookup(.Cells(lngRw, 3).Value2, rngUsedRange, 2, False)
Cells(lngRw, 9) = Cells(lngRw, 2)
' Area Location
.Cells(lngRw, 10) = Application.VLookup(.Cells(lngRw, 3).Value2, rngUsedRange, 3, False)
Next lngRw
End With

</CODE>

Jon

 

[JonXL]

I take it your issue is that the error is breaking your code. The ISERROR works as a formula-based type of error handling. But this same technique can't be used in code because the error isn't a formula error - it's a code error. You can try reworking what you have to use with the Evaluate function; this will give you the formula-based error handling.

Or you can use the code error handling methods by doing something like this (untested):

With objActiveWkB.Sheets("MyWorkBook")

    Err.Clear
    On Error Resume Next

    For lngRw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row


        'Site Location (1) & (2)
        .Cells(lngRw, 2) = Application.VLookup(.Cells(lngRw, 3).Value2, rngUsedRange, 2, False)
        If Err.Number > 0 Then .Cells(lngRw, 2) = "ADD_TO_DB"
        Err.Clear
        Cells(lngRw, 9) = Cells(lngRw, 2)
        ' Area Location
        .Cells(lngRw, 10) = Application.VLookup(.Cells(lngRw, 3).Value2, rngUsedRange, 3, False)
        If Err.Number > 0 Then .Cells(lngRw, 10) = "ADD_TO_DB"
        Err.Clear

    Next lngRw

    On Error Goto 0

End With

 

[joeu2004]

@Jon....

'Site Location (1) & (2)
.Cells(lngRw, 2) = Application.VLookup(.Cells(lngRw, 3).Value2, rngUsedRange, 2, False)
If IsError(.Cells(lngRw, 2)) Then
    .Cells(lngRw, 2) = "ADD_TO_DB"
    .Cells(lngRw, 9).ClearContents     ' ????
    .Cells(lngRw, 10).ClearContents    ' ????
Else
    .Cells(lngRw, 9) = .Cells(lngRw, 2)    ' correction: leading periods
    ' Area Location
    .Cells(lngRw, 10) = Application.VLookup(.Cells(lngRw, 3).Value2, rngUsedRange, 3, False)
End If

 

[JonXL]

Something learned everyday....

 

[JonRowland]

joeu2004 - perfect works a treat. Thanks for your help.

 

[JonRowland]

Sorry JonXL seems I missed your post for some reason.

The error didn't break the code just the result placed in the cell was #N/A.

Not tried your code as joe's worked for me. Plus as there is no Error, I believe I would have still had #N/A in the cell.

Jon

 

[JonXL]

Right... my 'solution' was no solution at all. I thought certain errors like that would break the code, #N/A must not be one of them - or else I have no idea at all what I'm talking about.

 

[joeu2004]

I thought certain errors like that would break the code, #N/A must not be one of them - or else I have no idea at all what I'm talking about.

That's the difference between Application.VLookup and [Application.]WorksheetFunction.VLookup. The latter throws a VBA error, which can be handled by using On Error Resume Next and testing Err.Number.

I wonder if Application.VLookup et al are deprecated. I cannot find any official documentation.