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

Me.TextBox1.Value = Application.WorksheetFunction.VLookup(Me.ComboBox2.Value, Range("a2:ao10000"), 41, 1, True)

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