Charlotte E. formulated on Wednesday :
None of them work???

The name refers to a range in another workbook:

TableRange ='D:\\Data\Spreadsheets\Tables\[Rates.XLS]Rates'!$A:$CV

I have no problem doing it in a worksheet cell as a normal formula, but how
to do it in VBA???


CE



Den 23.08.2011 19:53, Don Guillett skrev:
On Aug 23, 12:55 pm, "Charlotte wrote:
I have a named range on a worksheet, called 'TableRange'.

Making a vlookup in a worksheet formula will be like this
=VLOOKUP(F17,TableRange,2,1)

How to do this in VBA???

D = Application.Vlookup(cVal,"TableRange",2,1)
...doesn't work :-(

TIA

CE
You don't need the ,1 parameter as it is the default. See the help
index
= Application.Vlookup(cVal,range("TableRange"),2)
or try
= Application.Vlookup(cVal,[TableRange],2)

Open the workbook and ref the range as follows:

Set TableRange = _
Workbooks("Rates.XLS").Sheets("Rates").Range("$A:$ CV")

Otherwise, you'd have to use ADO to load the data into a recordset
without opening the file in Excel.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc