Lookup Formulas in macros

[Heather O'Malley]

I am trying to create a macro with a simple lookup function and copy. This
part is fine, however the file I will be looking up with be changing every
week - so I have added some variables.

I was thinking the spreadsheet directory and name could be typed into A10
and B10 of the sheet the data with be imported into "book2.xls" but am
having difficultly with related this to the formula...any help appreciated.

See attempt below:

Sub test()

Dim Importdir, Importfile

Importdir = Range("a10")
Importfile = Range("b10")

ChDir Importdir
Workbooks.Open Filename:=Importdir & Importfile,
UpdateLinks:=0

Windows("Book2.xls").Activate
Range("B1").Select
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC[-1],[Targetfile]Sheet1!R1C1:R3C2,2,FALSE)"
Range("B1").Select

Selection.AutoFill Destination:=Range("B1:B3"), Type:=xlFillDefault
Range("B1:B3").Select

Windows(Importfile).Activate
ActiveWindow.Close

End Sub

 

[Dave Peterson]

If you're building the formula in that adjacent cell, then you don't need to
open the other workbook--but you do have to be sure of the names (including the
worksheet name).

I opened a couple of workbooks and created an =vlookup() formula that pointed at
sheet1 of the second workbook:

When I closed the second workbook, my formula looked like:
=VLOOKUP(A1,'C:\My Documents\excel\[book 2.xls]Sheet1'!$A$1:$B$3,2,FALSE)
in R1C1 reference style:
=VLOOKUP(RC[-1],'C:\My Documents\excel\[book 2.xls]Sheet1'!R1C1:R3C2,2,FALSE)

So that's the kind of formula that I'd want to put into the cell.

Option Explicit
Sub test()

Dim Importdir As String
Dim Importfile As String
Dim myFormula As String
Dim myFormulaR1C1 As String

With Workbooks("book1.xls").Worksheets("sheet1")
Importdir = .Range("a10").Value
Importfile = .Range("B10").Value

'myFormula = "=VLOOKUP(A1,'" & Importdir & "\[" & Importfile _
& "]Sheet1'!$A$1:$B$3,2,FALSE)"

myFormulaR1C1 = "=VLOOKUP(RC[-1],'" & Importdir & "\[" & _
Importfile & "]Sheet1'!R1C1:R3C2,2,FALSE)"

.Range("b1").FormulaR1C1 = myFormulaR1C1

End With

End Sub

=========
As an alternative, you could use =indirect() to create that
workbook/worksheet/address string. But then the "sending" workbook has to be
open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you--it allows the sending workbook
to stay closed.

I am trying to create a macro with a simple lookup function and copy. This
part is fine, however the file I will be looking up with be changing every
week - so I have added some variables.

I was thinking the spreadsheet directory and name could be typed into A10
and B10 of the sheet the data with be imported into "book2.xls" but am
having difficultly with related this to the formula...any help appreciated.

See attempt below:

Sub test()

Dim Importdir, Importfile

Importdir = Range("a10")
Importfile = Range("b10")

ChDir Importdir
Workbooks.Open Filename:=Importdir & Importfile,
UpdateLinks:=0

Windows("Book2.xls").Activate
Range("B1").Select
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC[-1],[Targetfile]Sheet1!R1C1:R3C2,2,FALSE)"
Range("B1").Select

Selection.AutoFill Destination:=Range("B1:B3"), Type:=xlFillDefault
Range("B1:B3").Select

Windows(Importfile).Activate
ActiveWindow.Close

End Sub