VBA Insert function VLOOKUP into range,lookup range in other workbook

Question

VBA Insert function VLOOKUP into range,lookup range in other workbook. The file containing the lookup table is achieved using filename_AcctMgr = Application.GetOpenFilename(, , "Select Acct Mgr File", "Select"), then opening the file. Let's call this workbook2.

In workbook1 I am adding the VLOOKUP formula into "F2" and looking up Column "A" values in workbook2, columns A:C. I Then copy the formula to all rows of column "F".

I cannot find the syntax required to properly reference the workbook2 range in columns A:C.

ActiveCell.Formula = _
    "=VLOOKUP(activecell.offset(0,-5).address,'ws.name'!A:C,3,FALSE)"

Can anyone suggest the proper syntax?

Answer 1

Try this:

Range("F2").Resize(10).Formula = "=VLOOKUP(A2,[Book2]Sheet1!$A:$C,3,FALSE)"

Or

Range("F2:F10").Formula = "=VLOOKUP(A2,[Book2]Sheet1!$A:$C,3,FALSE)"

EDIT: Sorry I forgot the piece about the filename as a variable:

Dim MyFile As String
Dim vSplit As Variant
Dim iCnt As Integer

MyFile = Application.GetOpenFilename(, , "Select Acct Mgr File", "Select")

vSplit = Split(MyFile, "\")
iCnt = UBound(vSplit)
vSplit(iCnt) = "[" & vSplit(iCnt) & "]"
MyFile = Join(vSplit, "\")

Range("F2:F10").Formula = "=VLOOKUP(A2,'" & MyFile & "Sheet1'!$A:$C,3,FALSE)"    

You will need to add error handling in case someone clicks cancel. Also I doubt you want to add the formula to all rows in column f so just define the range you want. My examples is rows 2 to 10.

Answer 2

I am assuming you want the name of the sheet / range to be in a variable, rather than hard-coded. As it it, you have the name of the variable in the middle of your string, but it will be treated as a string, not a variable containing a string.

I suggest that you do something like the following:

Dim sheetName, lookupFrom, myRange           ' always declare your variables
sheetName = "This is the sheet name"         ' note I added some spaces to make it challenging
lookupFrom = ActiveCell.Offset(0, -5).address
myRange = "'" & sheetName & "'!A:C"          ' putting quotes around the string so it's always valid
ActiveCell.Formula = "=VLOOKUP(" & lookupFrom & "," & myRange & ", 3, FALSE)"

You can of course do this all at once - it just gets messy to look at:

ActiveCell.Formula = "=VLOOKUP(" & ActiveCell.Offset(0, -5).Address & ", '" & sheetName & "'!A:C, 3, TRUE)"

Further note - the sheetName can of course contain the name of the other workbook - but you need name of workbook AND sheet... so

sheetName = "[Book2]Sheet1"

would be fine.

In your example you used ws.name (without proper quoting) - but that would not have given you the full path since you need both the workbook and the worksheet name to make sure you reference the right data. Better be explicit - if Excel can make the wrong assumptions about what you want, it will - and you will be left scratching your head...

Since you actually showed you had opened the book, you have the name of the file (the workbook) in your variable filename_AcctMgr. Then you should be able to use:

sheetName = "[" & filename_acctMgr & "]Sheet1"

and take it from there.