combobox vlookup

**zak.horrocks** · 10-07-2014, 05:14 AM

Hello I am trying to get vba to vlookup a value from a combobox and return a value from the data sheet. My code so far is:

Private Sub TextBox1_Change()
Me.TextBox1.Value = Application.WorksheetFunction.VLookup(Me.ComboBox2.Value, "a2:ao10000", 41, 1, True)
End Sub

I am trying to look at the value in combobox2 and return the relevant data from that row in the data sheet in textbox1. A2:AO10000 is my data sheet range, I am trying to return the data in textbox1 from column a.

Any ideas?

Thanks

Zak

**LEEPRIESTENATOR** · 10-07-2014, 06:09 AM

Not exactly what you are looking for, but might help

http://www.youtube.com/watch?v=vkPoViUhkxU

**Richard Buttrey** · 10-07-2014, 06:14 AM

Hi,

Try

Please Login or Register  to view this content.

And if the table you're looking up is not on the active sheet you'll need to preface the Range() instruction with the Sheet name.

**karedog** · 10-07-2014, 06:26 AM

Hi,

There are several errors in your code :
- Your vlookup() function takes 5 arguments while maximum arguments for vlookup() is 4, look here : here
- As pointed by Richard, you must pass a range object in second argument, not a string
- Since the trigger is when the combobox2 's value is changed, then you should put the code in ComboBox2_Change() event, not TextBox1_Change() event
- You also must put an error trapper if the vlookup can't match any result (similiar to #N/A), otherwise the "“Unable to get the VLookup property of the WorksheetFunction Class” error will be raised. Look here for the solution : link