I'm trying to get a combobox to retrieve a set of values based on it's current value using a pre-defined table. The code currently doesn't do anything, as I've run into the problem that hlookup is apparently not defined! What am I supposed to type in a macro to get it to do this?
The code I'm using is as follows:
I just ran it the one time to initialize it, so the values are in fact, there.. it keeps giving me the not defined error thereafter.
Please Login or Register to view this content.
Last edited by Evalis; 06-01-2010 at 02:20 AM.
The correct syntax is
The variable race is undefined. Neither is x.Please Login or Register to view this content.
You should get in the habit of putting Option Explicit at the top of every module, and declaring all variables appropriate to usage.
Entia non sunt multiplicanda sine necessitate
I'm not sure I understand the 'option explicit' thing you mention. Race is defined already in this workbook, do I need to make it defined in VBA as well? x is a product of the for statement and doesn't need redefining I don't think (that part worked anyway).
This is still giving me an error, though it's different now.
Says: Unable to get the Hlookup property of the WorksheetFunction class
Is "race" a named range? This works only in functions in the worksheet. It can't just be used as a varible in VBA. To refer to a named range, you need the syntax Range(rangename). So, in this case:Race is defined already in this workbook
If you use Option Explicit at the top of your code, you must dim each variable before you can use it. This is a great way of catching typos and undeclared variables, for which you should make sure that you know their type and values.Please Login or Register to view this content.
Race is defined as a Table.. which I don't think is the same thing. I'm getting an error when I try this in any case: Method 'Range' of object '_Worksheet' failed. If I remember correctly, hlookup asks for a table and returns an error if you try to add in cells instead. Is there maybe a way to refer to a table? or am I on the wrong track?
How did you do that? What exactly is the "definition"?Race is defined as a Table
erm.. I don't know. I just defined it. The definition is =Race!$2:$30 referring to the worksheet. Apparently I'm on the wrong track.. I just assumed it was a table, since h and v lookup accepts it when running from the workbook, but doesn't accept cell values that I manually type in.
So, that looks as if you have a named range with the name "race" that refers to =Race!$2:$30 - Is that correct?
If so, this is a range of rows 2 to 30. Funny way to define a range, but possible. Now, what's in row 2?
I think you'd better post the workbook to see what's going on.
Oy.. it will probably be more complicated if I do that, but take a shot. The macro button is in the stats worksheet under the race column. The information it is pulling it from is in the race worksheet (I preloaded this without the hlookup and it worked fine). The table the hlookup is trying to gather information from is defined as race (it's actually an infinate range, though I tried changing it to something more linear and got the same error).
You are clearing the combobox as the first step in the macro, so ComboBox1.Value is empty.
By clearing the combobox, you invoke the ComboBox1_Change event again.
Now the combobox is cleared again, but since it was already clear, the code continues.
But ComboBox1.Value is still empty.
Therefore the Hlookup fails.
Redesign your code. What are you trying to do?
I removed the clear function, and still recieve the error. I was trying to prevent the combobox from continually adding new entries to the list. When I ran without the clear it created an entirely new set of duplicate information.
since you're working in a sheet module, you need to qualify the range reference for a range in another sheet.
But with the combobox clear command you'll always go through the code a second time. Might be a good idea to do the clear and populate only after you have done something with the looked up values.Please Login or Register to view this content.
Please Login or Register to view this content.
I've since opted to split that function into seperate initialization buttons.. actually I'd like to know how to initialize stuff when the excel spreadsheet loads, but I can deal with that on another post. Using the sheet reference worked though. Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks