Excel VlookUp left cell into VBA from another workbook

Question

Hi everybody, my VLookUp function is working so far, now I would like to convert it into a VBA function.

My VLookUp:

=VLOOKUP(A6;'[test.xls]Sheet1'!$A:$B;2;0) 'example for Cell A6, which can vary

Now I would like to get this into a VBA function which can be called. So if the function is called "A6" sould be "overwriten" by the left cell of where the function has been called.

I have tried following code:

Sub lookup()
        Dim x1 As String
        x1 = ActiveCell.Offset(0, -1).Select
        VLOOKUP(x1,'[test.xls]Sheet1'!$A:$B;2;0)
End Sub

What is necessary that it's working as expected?

I'm grateful for any tips.

Update:

Sorry, mabye it wasn't clear. "test.xls" is another workbook. The code should run inside the file called (e.g.) "missing.xls".

The range you want to do VLookUp on, is it in the same workbook or a different workbook? from your example, it looks like your range is in test.xls workbook, which may or may not be the same workbook? — Zac
– Zac, Commented Aug 8, 2018 at 11:37
Oh sorry, it's a different workbook with the name "test.xls" and the other workbook is called "missing.xls" in which I would like to run the code. — L.Writer
– L.Writer, Commented Aug 8, 2018 at 13:10

PaichengWu · Accepted Answer · 2018-08-08 12:11:06Z

2

There are two methods.

method 1: using `.formula`

sub method1()
    dim x1 as range

    set x1 = ActiveCell.Offset(0, -1)
    x1.formula = "=VLOOKUP(A6,'[test.xls]Sheet1'!$A:$B,2,0)"
end sub

method 2: using `.value`

sub method2()
    dim x1 as range

    set x1 = activecell.offset(0, -1)
    x1.value = application.vlookup(range("A6").value, workbooks("test.xls").worksheets("Sheet1").range("A:B"),2,0)
end sub

edited Aug 8, 2018 at 12:11

answered Aug 8, 2018 at 9:51

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

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

7 Comments

L.Writer Over a year ago

Almost as the solution from zipa... But with your examples I got just "syntax error", when I try to call the function. Any ideas? Thanks.

PaichengWu Over a year ago

@L.Writer which method? I just remove .select from my answer. Please try again.

L.Writer Over a year ago

I have tried again, but I still receive the syntax error if I try to call the function inside a cell.. :/

PaichengWu Over a year ago

@L.Writer Which method and which line?

L.Writer Over a year ago

Both methods, by debugging I don't get any error messages. Just if I try to call the function in a cell I'm receiving the syntax error. Trying to call the methods like this: =method1()

|

zipa · Accepted Answer · 2018-08-08 15:29:03Z

1

This one should do it:

Function MyLookup()

    Dim source As Workbook

    Dim current As Range

    Set current = Application.Caller
    Set source = Workbooks("test.xls")

    MyLookup = Application.WorksheetFunction.VLookup(current.Offset(0, -1).Value, source.Sheets(1).Range("A:B"), 2, False)

End Function

So Application.Caller gets the cell where the function is located and from there you can perform VLOOKUP() with Offset from that cell.

EDIT

For this lookup to work test.xls needs to be open.

edited Aug 8, 2018 at 15:29

answered Aug 8, 2018 at 10:21

zipa

28k6 gold badges45 silver badges62 bronze badges

4 Comments

L.Writer Over a year ago

Thanks in advance for the code, it seems that I can't figure out how to call the function (MyLookup). I have created a module (seems to be needed) and pasted the code inside this module, but when I enter inside (e.g.) "B7" =MyLookup() I got just "#VALUE!" - sorry, I'm very newbie with VBA :3

zipa Over a year ago

You are looking in B7 in which sheet? This performs lookup from Sheet1!A:B so your B7 is within that same range if you are trying in Sheet1.

L.Writer Over a year ago

I would like to call the function in B7 and then the value from A7 should be searched in another workbook called "test.xls".

L.Writer Over a year ago

Thanks for the update, but unfortunately it's still not working for me (same behaviour). By debugging the code I'm receiving following message: "runtime error "424": Object required" - do you may have an idea how to solve?

Collectives™ on Stack Overflow

Excel VlookUp left cell into VBA from another workbook

Update:

2 Answers 2

method 1: using `.formula`

method 2: using `.value`

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update:

2 Answers 2

method 1: using .formula

method 2: using .value

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related

method 1: using `.formula`

method 2: using `.value`