Perform a Vlookup using a Form ComboBox as the Lookup Value

[Richard_Thornton]

I am a poor student at VBA so this should be easy for you pro's out there.

Below is as far as i have got. No errors are thrown up but it doesn't do as i expect it too.

Any help appreciated.

Thanks in advance.

Please Login or Register  to view this content.

[buran]

Hi,
it's generally a bad practice to turn errors off, unless you have a reason to do so. it's certainly not wise when debug code. for start comment out

Please Login or Register  to view this content.

so you will see if any error pops.
do you have named range Tariff2 in sheet Tarrifs?

[Richard_Thornton]

Okay thanks Buran.

Yes the range Tariff2 is set in the Tariff sheet. In theory should the code I entered work? (am i barking up the right tree)

[Norie]

How are you populating the combobox?

[buran]

it looks right to me. see the attached example

[Richard_Thornton]

Norie,

The combobox is populated with the below code.

Please Login or Register  to view this content.

[Richard_Thornton]

it looks right to me. see the attached example

Hi Buran,

Yours works absolutely correct the only thing is you have defined the range and i need a dynamic range as some times there maybe more or less options in the table array on the lookup.

[buran]

My code still works just fine if I define named range and use it in the vlookup.

[buran]

By the way, what is the scope of the defined named range on the worksheet Tarrifs. Is it the workbook or just the worksheet on which the range is?

[Norie]

Are the ranges Tariff and Tariff2 related in anyway?

[Richard_Thornton]

Hi Buran,

The Range is A2 to D7 at the moment but i want it to be able to change to D8 if another is added etc.

Thanks for your help.

[Richard_Thornton]

Hi Norie,

The Tariff range is A2 to A7 the Tariffs sheet.
The Tariff2 range is A2 to D7 on the Tariffs sheet.

Thanks for your time.

[buran]

Hi Richard.
When I asked about the scope I mean what is the scope of the named range (where it is visible from). When you define a named range from Formulas->Name Manager, you can set the scope to workbook or to just one sheet. There is possibility that the named range Tariif2 scope is set to Tarrifs sheet, so it is not visible when you show form when Billing is active sheet.
Consider to upload a sample workbook, otherwise it's a wild guess and may take a long time to solve the issue.

[Richard_Thornton]

Hi Buran,

The scope is set as workbook on both Tariff and Tariff2. I will upload the Workbook shortly first i need to get rid of all my embaressing workings out.

[Norie]

Richard

Instead of VLookup you can use the ListIndex if the combobox to get the row and return the value from column B.

Please Login or Register  to view this content.

[Richard_Thornton]

I have it working perfectly now thanks guys. One of My Named Ranges was set wrong so i started again and its now doing as it should.

I Set this as the named range TariffName =OFFSET(Tariffs!$A$2,0,0,COUNTA(Tariffs!$A$2:$A$200),1)
and this as the vlookup range TariffRng =OFFSET(Tariffs!$A$2,0,0,COUNTA(Tariffs!$A$2:$A$200),4)

The the following code

Please Login or Register  to view this content.

Thank you both for your help your suggestions helped me greatly to get the finished result.