VLookup a named range

D

Don Guillett

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
Click to expand...
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)
 
C

Charlotte E.

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
 
G

GS

Charlotte E. expressed precisely :
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
Click to expand...

D = Application.WorksheetFunction.VLookup(cVal, "TableRange",2)
 
C

Charlotte E.

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:
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
Click to expand...
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)
Click to expand...
 
G

GS

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:
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
Click to expand...
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)
Click to expand...
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vba range in vlookup 4
Range VBA: double entry table 2
Range vba: double entry table 0
Best way to run Find or Vlookup down specific column in range 2
Simple Vlookup Loop 0
Add value to the content of a cell with Vlookup 2
if iserror then value = zero problem 1
Can Application.Vlookup access a range in a file which is not open? 4

Top