VLOOKUP VBA COMBOBOX

[tc1983]

I am trying to get a Combobox to be the lookup value. Have looked on many sites and cannot get to work, code is below

tradeid = cmbtrade.Value
dateopen = txtdateopen.Value

dateopen = Application.WorksheetFunction.VLookup(tradeid, Sheets("Open_Positions").Range("A3:L49"), 2, False)

 

[WalkerEvans]

tc1983,

You're using the same variable name for two completely different purposes; this is never a good idea as it can confuse people and lead to difficulties trying to debug code that isn't working. See if this modification helps:

[COLOR=red][highlight]DataGrab[/highlight][/color] = Application.WorksheetFunction.VLookup(tradeid, Sheets("Open_Positions").Range("A3:L49"), 2, False)

You can use any name you wish; the above is just a placeholder. The important part is to get away for having one variable name mean two different things.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[tc1983]

Sorry I am so confused with this all. Starting from the begining I have an xls with a unique number (tradeid). I want this to be the point of focus to return other values with the same row. VLOOKUP is the way forward? I am struggling to get my form text boxes to display the results of a vlookup. any help please

thanks

 

[Fr33dan]

Where are you trying to set the return of the lookup to the value of ComboBox? What is the name of the ComboBox?

 

[tc1983]

setting the return to a text box within a form.

combobox is named cmbtradeid
textbox is named txtdateopen (getting a date)
table range is A3:L49
Returning value from column 2.

thanks for your help.

 

[tc1983]

this is what i have

tradeid = Me.cmbtradeid.Value

txtdateopen.Value = Application.WorksheetFunction.VLookup(tradeid, Range("A3:L49"), 2, False)

have assigned a command button to call and getting this error

"unable to get the vlookup property of the worksheet function class".

Ideally i want this to run once the trade id is selected from the combo box.

 

[Fr33dan]

Try replacing Application.WorksheetFunction.VLookup with Application.VLookup

 

[tc1983]

Getting a runtime error

Could not set the value property. type mismatched!!??

 

[Fr33dan]

Me.cmbtradeid.Value is returning a string so vlookup is looking for a string. you'll have to convert the string to an actual number as you get it:

tradeid = Clng(Me.cmbtradeid.Value)

 

[WalkerEvans]

Since you're working with dates, which is always a tricky proposition, everything becomes much more critical. Your dates must be in EXACTLY the same format, especially since the FALSE switch means you'll only accept a perfect match. This means that a search value of 8/6/07 will not find a table value of 08/06/07. This might not be too bad if you're the only one using this, but if anyone else is involved you'd better make sure that the date format in your input is set so that only the format you want can be entered.

From the error you indicate you're probably getting something other than a date format when you set tradeid. Have you used a Dim statment to set tradeid as a date prior to calling it? If not, I guarantee it's defaulting to a format that will never yield a match against your table.

Take a look, and post back here if you're still getting errors.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[tc1983]

Superb, thanks for all your help!