Dealing With An Anticipated Error In A Loop

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

Code:
.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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Use Application.Vlookup and store the result in a Variant. Then test it with Iserror(variable) and react accordingly.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That makes too much sense. Thanks Rory.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That makes too much sense. Thanks Rory.

But maybe not enough ...

Code:
       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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Remove the Worksheetfunction. part:

Code:
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,371
Messages
5,635,876
Members
416,886
Latest member
coreyalaurence37

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