CODEWITHSUNDEEP
vbaexcel
Thrazzon
Thrazzon

Reputation: 23

Excel 2016 Vlookup Function in VBA

I need some help with Excel's Vlookup function. I have 2 worksheets. On the first there's a Company Code, on the second I need to find values based on the company code. The relevant code is:

Dim Compcode, AUC, OB As String
Dim WS1, WS2 As Worksheet
Set WS1 = ThisWorkbook.Worksheets("Main Sheet")
Set WS2 = ThisWorkbook.Worksheets("Data")
Compcode = WS1.Cells(2, 1).Value 'Company code WS1 A2

AUC = Application.WorksheetFunction.VLookup(Compcode, WS2.Range("A2:C30"), 2, False)
OB = Application.WorksheetFunction.VLookup(Compcode, WS2.Range("A2:C30"), 3, False)

I've tried to modify the Dim for each of the variables and the Vlookup function itself, but I always get to the same error message:

Run-time error '438': Object doesn't support this property or method

Upvotes: 2

Views: 4509

Answers (2)

CallumDA
CallumDA

Reputation: 12113

WorksheetFunction.Vlookup is very volatile to the inputs and can throw runtime errors easily, as you've encountered.

Your issue could be one of a number of things but my suggested solution would be to just use Find instead:

Sub test()
    Dim Compcode As Variant, AUC As Variant, OB As Variant
    Dim WS1 As Worksheet, WS2 As Worksheet

    Set WS1 = ThisWorkbook.Worksheets("Main Sheet")
    Set WS2 = ThisWorkbook.Worksheets("Data")
    Compcode = WS1.Cells(2, 1).Value 'Company code WS1 A2

    Dim rng As Range
    Set rng = WS2.Range("A2:C30").Find(Compcode, , , xlWhole)

    If Not rng Is Nothing Then
        AUC = rng.Offset(0, 1).Value
        OB = rng.Offset(0, 2).Value
    End If
End Sub

Upvotes: 4

Vityata
Vityata

Reputation: 43585

The problem can be in many places. Anyhow obviously, VLOOKUP is a bit picky on numbers and strings, thus the Compcode should be a Variant

Something like this would work out:

Option Explicit

Public Sub TestMe()

    Dim Compcode        As Variant
    Dim AUC             As String
    Dim OB              As String

    Dim WS1             As Worksheet
    Dim WS2             As Worksheet

    Set WS1 = ThisWorkbook.Worksheets("Main Sheet")
    Set WS2 = ThisWorkbook.Worksheets("Data")

    Compcode = WS1.Cells(2, 1).Value

    Dim rngRange As Range

    Set rngRange = WS2.Range("A2:C30")

    AUC = Application.WorksheetFunction.VLookup(Compcode, rngRange, 2, False)
    OB = Application.WorksheetFunction.VLookup(Compcode, rngRange, 3, False)

End Sub

You should declare explicitly every variable, this is not c++/c to declare them with a comma and to hope that the VBEditor would understand.

I have also changed the formula to a range.

Upvotes: 3

Related Questions