Vlookup across Workbook

[twmills]

I have a lengthy VBA macro, and in it I'm trying to add a VLOOKUP formula that brings in the Branch Number (column B in the Temp_Holdings_Combined) file, into column G of the NuveenCRM_Final spreadsheet. Using the Account number (column D in NuveenCRM_Final) as the Lookup Value... to match up to column A of Temp_Holdings_Combined.

So in cell G2 of the NuveenCRM_Final workbook, I'm looking to use VBA to input this:

=VLOOKUP(D2,[Temp_Holdings_Combined.xlsx]Sheet1!$A1:$B6000,2,FALSE)

Then have the formula paste down to the bottom of the table in NuveenCRM_Final

I recorded (Record Macro) myself inputing this formula, then used that macro from the recording and it doesn't work. I'm getting a debug error. Not sure if it doesn't like me crossing over workbooks or what.



Thank you!

[arnelgp]

i think you forgot to add single quote (') on the workbook name:

=VLOOKUP(D2,'[Temp_Holdings_Combined.xlsx]Sheet1'!$A1:$B6000,2,FALSE)

[Paul_Hossler]

Option Explicit


Sub Macro1()
Dim rTop As Range, rBottom As Range

'Dealer number range
Set rTop = ActiveSheet.Range("D2")
Set rBottom = rTop.End(xlDown)

'Branch number range
Set rTop = rTop.Offset(0, 3)
Set rBottom = rBottom.Offset(0, 3)

'insert branch number formula
rTop.FormulaR1C1 = "=VLOOKUP(RC[-3],[Temp_Holdings_Combined.xlsx]Sheet1!C1:C2,2,FALSE)"

'fill to bottom
rTop.AutoFill Destination:=Range(rTop, rBottom)
End Sub





You only need the single quotes if there are spaces in the file name:



ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'[Temp Holdings Combined.xlsx]Sheet1'!C1:C2,2,)"

[twmills]

Option Explicit


Sub Macro1()
Dim rTop As Range, rBottom As Range

'Dealer number range
Set rTop = ActiveSheet.Range("D2")
Set rBottom = rTop.End(xlDown)

'Branch number range
Set rTop = rTop.Offset(0, 3)
Set rBottom = rBottom.Offset(0, 3)

'insert branch number formula
rTop.FormulaR1C1 = "=VLOOKUP(RC[-3],[Temp_Holdings_Combined.xlsx]Sheet1!A1:B2000,2,FALSE)"

'fill to bottom
rTop.AutoFill Destination:=Range(rTop, rBottom)
End Sub





I have the VLOOKUP macro exactly how it looks above, but for some reason - on its own - the code is inputting single quotes around the range (as seen below), which is causing the formula to bomb. Once I manually remove the single quotes it works fine. Any ideas to get around excel from doing this?

=VLOOKUP(D2,[Temp_Holdings_Combined.xlsx]Sheet1!'A1':'B2000',2,FALSE)

[p45cal]

Any ideas to get around excel from doing this?

=VLOOKUP(D2,[Temp_Holdings_Combined.xlsx]Sheet1!'A1':'B2000',2,FALSE)
Yes, you're mixing A1 and R1C1 references.
In the code you could use
.Formula =
or
.FormulaR1C1 =

One of the following lines alone will enter the formula you need in all the cells (the whole macro only needs to be one line):

Range(ActiveSheet.Range("D2"), ActiveSheet.Range("D2").End(xlDown)).Offset(, 3).Formula = "=VLOOKUP(D2,'[Temp_Holdings_Combined.xlsx]Sheet1'!$A$1:$B$6000,2,FALSE)"
Range(ActiveSheet.Range("D2"), ActiveSheet.Range("D2").End(xlDown)).Offset(, 3).FormulaR1C1 = "=VLOOKUP(RC[-3],'[Temp_Holdings_Combined.xlsx]Sheet1'!R1C1:R6000C2,2,FALSE)"
The second one is 'safer' (will need less adjustment if the cells you want to fill don't start in row 2).
I've left the single quotes in, so should you need to adjust the sheet name or file name and there are spaces in either, you won't have to work out where they belong; Excel will remove them from the formula on the sheet if they're not needed.

[Paul_Hossler]

I'm GUESSING that it was caused by mixing R1C1 and A1 address styles





Sub Macro2()
Dim rTop As Range, rBottom As Range

'Dealer number range
Set rTop = ActiveSheet.Range("D2")
Set rBottom = rTop.End(xlDown)

'Branch number range
Set rTop = rTop.Offset(0, 3)
Set rBottom = rBottom.Offset(0, 3)

'insert branch number formula
rTop.Formula = "=VLOOKUP(D2,[Temp_Holdings_Combined.xlsx]Sheet1!$A:$B,2,FALSE)"
'fill to bottom
rTop.AutoFill Destination:=Range(rTop, rBottom)
End Sub


29417

[twmills]

Yes! That did the trick.

I'll keep this open until I finish testing, just to make sure everything is working the way I need it. Once everything looks good I'll close this thread out.

Thanks everyone. Much appreciated!