Named range in VBA Vlookup?

**Macdave_19** · 10-31-2007, 09:08 AM

Chik wee (as Borat might say),

I'm wondering? is there any way that you can use a named range for the array in a vlookup that is done in VBA?

my named range is "Lists" and here's my code:

Please Login or Register  to view this content.

As ever people, thanks for looking & god bless this website or i'd be jobless!! lol

**StephenR** · 10-31-2007, 09:51 AM

I'd have thought Range("InsertNameHere") would work, i.e. Range("Lists")

**Macdave_19** · 10-31-2007, 10:23 AM

Here's the code so far:

Please Login or Register  to view this content.

Here's my newest error:

Unable to get the Vlookup property of the worksheetfunction class

any ideas?

**royUK** · 10-31-2007, 10:54 AM

I woulduse the VBA Find method

**Norie** · 10-31-2007, 11:13 AM

If that's the error then it probably means the lookup is returning N/A.

How have you populated your combobox?

What is it you are actually looking up?

**Macdave_19** · 10-31-2007, 11:17 AM

To begin with the combobox is empty so it looks up nothing initially, maybe thats the problem? should i place the vlookup in the combobox_change event?

**Macdave_19** · 10-31-2007, 11:19 AM

Thanks for the question you asked, it pointed me in the right direction,

i placed the lookup in the event change of the combobox and now it works,

it was obviously failing since the combobox was empty initially!!

get in!!

**Norie** · 10-31-2007, 11:20 AM

That isn't really what I'm asking.

How are you populating the values in the combobox dropdown?

**Macdave_19** · 10-31-2007, 11:23 AM

Sorry i got a bit excited heres how i poulated the combobox mate:

Please Login or Register  to view this content.

**Norie** · 10-31-2007, 11:27 AM

Well if that's how you've populated the combobox there's no need for either a lookup or find.

The ListIndex of the combobox will correlate to the row number the data is on.

FrmGet.TextBox1.Value = Sheets("List").Range("A" & FrmGet.cbAgent.ListIndex+1)