Excel VBA hlookup function

debugcn Published at Dev

tadalendas

I am trying to use the excel VBA HLookup function. I have tried it two ways and both produce error. Could anyone explain me what is that I am doing wrong?

First try:

    lookupValue = Worksheets(1).Name & "!A1"
    tableArray = Worksheets(3).Name & "!$A$1:$" & Col_letter & "$1"
    Worksheets("Comparison").Cells(1, 2).Value = "=HLookup(" & lookupValue _
    & ";" & tableArray & ";1;FALSE)"

Second try:

    tda = Worksheets(1).Cells(1, 1).Value ' I also tried using tda without .Value
    Table = Worksheets(3).Range(Cells(1, 1))
    Worksheets("Comparison").Cells(1, 2).Value = WorksheetFunction. _
    HLookup(tda, Table, 1, False)

Rory

For your first one, you need to use US regional settings, so a comma separator, and you should really enclose the sheet names in single quotes in case they contain spaces or look like special names (e.g. dates):

lookupValue = "'" & Worksheets(1).Name & "'!A1"
tableArray = "'" & Worksheets(3).Name & "'!$A$1:$" & Col_letter & "$1"
Worksheets("Comparison").Cells(1, 2).Formula = "=HLookup(" & lookupValue _
& "," & tableArray & ",1,FALSE)"

and for the second you have to use a range object for the table argument:

tda = Worksheets(1).Cells(1, 1).Value ' I also tried using tda without .Value
Set Table = Worksheets(3).Range(Worksheets(3).Cells(1, 1), Worksheets(3).Cells(1, Col_letter))
Worksheets("Comparison").Cells(1, 2).Value = WorksheetFunction. _
HLookup(tda, Table, 1, False)

I have assumed Table is declared as Variant, Object or Range.

Note you will still get a run-time error if there is no match for your lookup value.

Collected from the Internet

Please contact [email protected] to delete if infringement.