Dealing With An Anticipated Error In A Loop

[Ark68]

I have this line of code in my application that I know in advance there may be times it will err out, usually because the value .cells(i,3) doesn't exist ... yet.

.Cells(i, 4) = Application.WorksheetFunction.VLookup(.Cells(i, 3), ws_rd.Range("A:C"), 3, False)

When it does err, I would like to recognize that by executing some code exclusive to that scenario. Since this line is within a loop though, I don't want this to stop the loop, but carry on.

 

[RoryA]

Use Application.Vlookup and store the result in a Variant. Then test it with Iserror(variable) and react accordingly.

 

[Ark68]

That makes too much sense. Thanks Rory.

 

[Ark68]

That makes too much sense. Thanks Rory.

But maybe not enough ...

       For i = 2 To lrow
            str_recid = Application.WorksheetFunction.VLookup(.Cells(i, 3), ws_rd.Range("A:C"), 3, False)
            If IsError(str_recid) Then
                MsgBox "Value missing."
                'Stop
            End If
       Next i

I'm encountering an error whether I put the vlookup before or after the IsError.

 

[RoryA]

Remove the Worksheetfunction. part:

str_recid = Application.VLookup(.Cells(i, 3), ws_rd.Range("A:C"), 3, False)

and I assume that str_recid is actually declared as Variant as I suggested?

 

[Ark68]

Yeah ... things are going well now. It was the worksheetfunction that was the issue.
I'll need to do some research whn to use it and when not to!

 

[RoryA]

If you use Worksheetfunction it will cause a run-time error in the code when the function fails. If you use the Application version, it returns an error value that you can test with IsError.