VLOOKUP with variable workbook and worksheet names in macro

[msquared99]

I need some help. I have a macro where I am doing a VLOOKUP from one sheet to another. The issue is that the workbook that the macro opens will have a different name and the worksheet within that workbook will have a different name.

Here is what the VLOOKUP looks like in the spreadsheet: =VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)

Here is what I need it to look like in the macro code: "=VLOOKUP(RC[-1],'"
& fName1 & WBName & "'!R2C14:R7C14,1,0)

So, fName1 replaces SalesLimit1-01262017-AM.xls (the variable workbook name) and WBName replaces SalesData-012620 (the variable
tab name)

Cross posted here: http://chandoo.org/forum/threads/vlookup-in-macro.32913/


Sub GetFile()
Dim fName1 As Variant, wbo As Workbook
Dim fName2 As Variant, wba As Workbook
Dim WBName As Worksheet
fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the prior spreadsheet:")
If fName1 = False Then Exit Sub
Set wbo = Workbooks.Open(fName1)
wbo.Activate 'New code
Set WBName = wbo.Worksheets(1) 'New code

fName2 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the current spreadsheet:")
If fName2 = False Then Exit Sub
Set wba = Workbooks.Open(fName2)

wba.Activate

Range("M2").Value = "1"
Range("M2").Select
Selection.AutoFill Destination:=Range("m2:m" & Cells(Rows.Count, "A").End(xlUp).Row)

Range("N2").FormulaR1C1 = "=RC[-13]&RC[-10]"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & Cells(Rows.Count, "A").End(xlUp).Row)

Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & fName1 & WBName & "'!R2C14:R7C14,1,0)"
'Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row)

wbo.Close savechanges:=True
wba.Close savechanges:=True
End Sub

[Paul_Hossler]

Not 100% what the question is, but I did simple string construction using the original values to make sure that I could reproduce your known inputs


if you use



"=VLOOKUP(RC[-1],'[" & fName1 & "]" & WBName & "'!R2C14:R7C14,1,0)"


it generated the equivalent formula





Sub test()
Dim fName1 As String, WBName As String
Dim s As String, sNew As String

s = "=VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)"

fName1 = "SalesLimit1-01262017-AM.xls"
WBName = "SalesData-012620"

sNew = "=VLOOKUP(RC[-1],'[" & fName1 & "]" & WBName & "'!R2C14:R7C14,1,0)"

MsgBox s & vbCrLf & sNew

End Sub

[msquared99]

Thanks Paul.

When I run the macro I get a Run-Time Error 438 on the VLOOKUP.

The issue is I do not know what WBName will be, the name varies and is not static. Same with fName1.

So how do I get the variable worksheet name and set it?

[Paul_Hossler]

Your first formula didn't look correct to me



Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & fName1 & WBName & "'!R2C14:R7C14,1,0)"



So I tweaked it so that it would give the same results as one you said was working




Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)"



if I used the same WB and WS names, then I got the same result



Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & fName1 & "]" & WBName & "'!R2C14:R7C14,1,0)"





So how do I get the variable worksheet name and set it?


Good question, many variables

You remember it in variable when you open it



Set wbo = Workbooks.Open(fName1)

Set WBName = wbo.Worksheets(1) 'New code

fName2 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the current spreadsheet:")



so why can't you use them?

[msquared99]

When I set up everything like you have it I get a Run-time error 438 Object doesn't support this property or method on the VLOOKUP.

[Paul_Hossler]

When I set up everything like you have it I get a Run-time error 438 Object doesn't support this property or method on the VLOOKUP.



VLOOKUP is only used as part of a string, so I don't understand how an object-related error would be generated because of the VLOOKUP



Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & fName1 & "]" & WBName & "'!R2C14:R7C14,1,0)"

[msquared99]

I'm pretty bumfuzzled. I've tried several variations of code and keep getting a 438 Run-Time Error on the VLOOKUP.

[msquared99]

I figured it out Paul. Since the workbook only has one worksheet in it I do not need to assign in WBName.

This code worked:

Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & fName1 & "]'!R2C14:R7C14,1,0)"