call VLOOKUP from Macro with Variables
 

Dear Friends,

I want to call VLOOKUP from a macro as follows

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(name1,'[file2]sheet2'!name2:name3,5,0)"

where

name1 - is the reference name of the values in file 1 (active)
file2 - is the variable that contains the name of the file I want t
get the values
sheet2 - is the variable that contains the name of the sheet of file2
name2:name3 - is the table of [file2]sheet2 where name2, name3 ar
declared reference names

What is the correct way to call this function?

Thanks,
G

--
Message posted from http://www.ExcelForum.com

call VLOOKUP from Macro with Variables
 

The basic code is something like this :-

Code
-------------------

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("Mylookup")
ActiveCell.Value = _
Application.WorksheetFunction.VLookup(name1, MyRange, 5, 0)

-------------------


--
Message posted from http://www.ExcelForum.com

call VLOOKUP from Macro with Variables
 

BrianB wrote:
The basic code is something like this :-

Code
-------------------

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("Mylookup")
ActiveCell.Value = _
Application.WorksheetFunction.VLookup(name1, MyRange, 5, 0)

-------------------


OK.
But if instead of string "Sheet1" Ihave a variable s = "Sheet1"
and instead of Range("A1:D100") I have Range(a:d) where a is th
reference name of the data in col A and d the reference name of th
data in column D, how do I formulate the code?

Thanks,
G

--
Message posted from http://www.ExcelForum.com

call VLOOKUP from Macro with Variables
 

Replace "Sheet1" with s and "MyLookup" with "A1:D100" in my code.

I do not understand your a:d nor see how it could work

--
Message posted from http://www.ExcelForum.com