Excel VlookUp left cell into VBA from another workbook
Clash Royale CLAN TAG#URR8PPP
Excel VlookUp left cell into VBA from another workbook
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.
Sorry, mabye it wasn't clear. "test.xls" is another workbook. The code should run inside the file called (e.g.) "missing.xls".
VLookUp
test.xls
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
Aug 8 at 13:10
2 Answers
2
There are two methods.
method 1: using .formula
.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
.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
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.
– L.Writer
Aug 8 at 11:29
@L.Writer which method? I just remove
.select
from my answer. Please try again.– PaichengWu
Aug 8 at 12:10
.select
I have tried again, but I still receive the syntax error if I try to call the function inside a cell.. :/
– L.Writer
Aug 9 at 4:36
@L.Writer Which method and which line?
– PaichengWu
Aug 9 at 4:49
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()
– L.Writer
Aug 9 at 4:51
=method1()
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.
Application.Caller
VLOOKUP()
Offset
EDIT
For this lookup to work test.xls
needs to be open.
test.xls
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– L.Writer
Aug 8 at 11:26
=MyLookup()
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
.– zipa
Aug 8 at 11:40
B7
Sheet1!A:B
B7
Sheet1
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
Aug 8 at 13:38
@L.Writer Please see the edit.
– zipa
Aug 8 at 15:29
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?
– L.Writer
Aug 9 at 4:34
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
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 intest.xls
workbook, which may or may not be the same workbook?– Zac
Aug 8 at 11:37