LOOKUP function in VB Macro.

[Art Du Rea]

Remove NOGI and NOGO for e-mail address
(NO Garbage In - NO Garbage Out)

Esteemed Group Participants and Lurkers:

I can't seem to get the lookup function to execute in a
complex macro I am writing. Here is what I am trying to
do:

Workbook with active sheet with macro:
H:\Test\MyTestBook.xls Sheet1

Workbook with lookup source:
D:\Work\My WorkBook.xls SheetX

Lookup Table is named "myTable"

The lookup table works fine, but I can't get it to work
inside the macro. I have tried cut and paste and other
things, but haven't gotten anything to work.

Here is the closest I can come:

Dim i As Long
i = 7
Cells(1, i) = Application.WorksheetFunction.Lookup_
(Cells(2,i), 'D:\Work\My WorkBook.xls'!myTable)

Any suggestions as to how to get this to work?

Thank you for any comments, suggestions, or assistance.

Blessings in abundance, all the best, and ENJOY!

Art Du Rea Carlisle, PA USA

 

[Tom Ogilvy]

Cells(1, i) = Application.Lookup_
(Cells(2,i), Workbooks("My
WorkBook.xls").Names("myTable").ReferstoRange)

My Workbook.xls must be open.