Excel VBA - Use VlookUp Inside Is Error

Question

I need to do a vlookup through a range of cells, and depending on it returning a value or a #N/A, I want to do some action on it.

I tried to place the vlookup inside the iserror function but that didn't work.

This is what i tried next but is also not working:

Set costCentreMapping = Workbooks.Open(Filename:="C:\mapping.xlsx")
Sheets("Sheet2").Activate
Dim CostCentreRange As Range
Set CostCentreRange = Range("A4:E2967")

    Set test1 = Application.WorksheetFunction.VLookup(appid, CostCentreRange, 2, False)

            If IsError(test1) Then 

                appid.Offset(columnoffset:=15) = "value1"

            End If

What do you recommed me to do?

Thanks

to do it that way you need to remove the .WorksheetFunction from both VLOOKUPS. This will allow the passing of the error into the variable. — Scott Craner
– Scott Craner, Commented Mar 20, 2017 at 17:59
doesn't VLOOKUP return a value? I don't think you necessarily need to 'set' test1 and test 2 (albeit not tested) — sous2817
– sous2817, Commented Mar 20, 2017 at 18:02
@AntónioPires what exactly are you Setting with the VLookup ? what value are you trying to get from it ? — Shai Rado
– Shai Rado, Commented Mar 20, 2017 at 18:08
@ShaiRado I'm using the value of first argument to look for it in another table. If that value doesn't exist in that table I want to do some action, and if it exists i want to do some different action — António Pires
– António Pires, Commented Mar 20, 2017 at 18:11

Scott Craner · Accepted Answer · 2017-03-20 18:42:36Z

1

As I said in my comment:

Set costCentreMapping = Workbooks.Open(Filename:="C:\mapping.xlsx")

Dim CostCentreRange As Range
Set CostCentreRange = costCentreMapping.Sheets("Sheet2").Range("A4:E2967")

Dim test1 as variant 'the variable must be a variant to accept an error.
    test1 = Application.VLookup(appid, CostCentreRange, 2, False)

            If IsError(test1) Then 

                appid.Offset(columnoffset:=15) = "value1"

            End If

answered Mar 20, 2017 at 18:42

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2017-03-20 18:11:34Z

1

Your entire code (that you posted above) could look like the shorter version below:

If IsError(Application.VLookup(appid, CostCentreRange, 2, False)) Then ' <-- not found in VLookup
    appid.Offset(, 15) = "value1"

    If customercountry = "UNITED KINGDOM" Then
        If IsError(Application.VLookup(billService, billableRange, 3, False)) Then
            appid.Offset(, 14) = "value2"
        End If
    End If
End If

answered Mar 20, 2017 at 18:11

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

8 Comments

António Pires Over a year ago

This is what i had in the first attempt. But it results on an error, it doesn't run.

David Zemens Over a year ago

This will give an error 5 Invalid Procedure Call or Argument if your range object(s) are Nothing.

Shai Rado Over a year ago

@DavidZemens are you referring to me or Antonio ?

David Zemens Over a year ago

@ShaiRado both :) If the range arguments can't be evaluated, then even wrapping the Application.Vlookup in an IsError function will still raise an error.

António Pires Over a year ago

I get a runtime error 1004: Unable to get the Vlookup property of the worksheetfunction class

|

Collectives™ on Stack Overflow

Excel VBA - Use VlookUp Inside Is Error

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related