CODEWITHSUNDEEP
excelvlookupvba
António Pires
António Pires

Reputation: 23

Excel VBA - Use VlookUp Inside Is Error

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

Upvotes: 0

Views: 125

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

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

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 1

Related Questions