Use the .List method to read numbers from dropdown (combo)

[0123ren]

Hey,

I am working on a bit of code that will read data from a series of columns in a database, a dropdown menu is used to display unique values from one of the columns. I then want to read the value that has been selected from this dropdown and then display a value in the same row but on a different column in a separate listbox (similar to vlookup). The code I have works with a database full of strings but falls down when the values in the dropdown are number (error type mismatch).

I think this is a problem with the .List method of the dropdown it simply refuses to read numbers, even when the column arrar I am reading from is stored as variant.

I hope I have explained my problem clearly, if not please ask, I appreciate any help that someone can give me. Full code or spreadsheet is available upon request.

Thanks in advance

PS here is the part of the code that reads the value selected from the dropdown and produces the corresponding value from a different column in a list box. As I say it works perfectly when the value in the dropdown is a string but refuses to work with numbers.

Sub LoadPressures()
Dim listSeatDia
Dim Data
Dim UniquePressures
Dim Pressures

'Selects data range to be worked upon
With Worksheets("Data")
Data = Intersect(.UsedRange, .Range("k:q"))
End With

'Removes all items from the list box
Sheet1.ListBoxes("Concat_SD_P").RemoveAllItems

'Reads what value is selected in the dropdown, this will effect what is shown in the listbox
With Sheet1.DropDowns("SeatDia")
listSeatDia = .List(.ListIndex)
End With

'This pulls data that is selected in column 1 and replaces it with data
'on column 7 in the same row
UniquePressures = UNIQUEVALUES(Data, listSeatDia, 1, 7)

Pressures = SORTSDARRAY(UniquePressures, Ascending)

'lists all of the unique items in column 7 that have been selected and places these in the list box
For i = 0 To UBound(Pressures, 1)
ListBoxes("Concat_SD_P").AddItem Pressures(i)
Next
End Sub

 

[RoryA]

Where does the error occur? You call two procedures that you haven't posted the code for - I guess it's in one of those as you haven't typed any variables in the posted code.

 

[0123ren]

Yes, UNIQUEVALUES as I am aware just compares all values in the array and stores all of the uniqe values and removes all blanks.

And SORTSDARRAY basically sorts the unique list in ascending or descending order.

When the code falls down simply 'type mismatch' is reported and it doesn't highlist the actual area where the code failed or where it originated from.

I could send the spreadsheet if needed complete with the 2 procedures called?

 

[RoryA]

The fact that you don't get a debug option makes me think the error is not in the code you posted (since you have no error handlers) so yes, it might be best to send the workbook, unless you can post it on a site like box.net?

 

[0123ren]

Ok thanks for taking the time to view it, I think I have correctly set up a link to where the folder is stored on box.net.

Please let me know if you need additional info.

Thanks in advance

http://www.box.net/shared/1qhc0iijf8

 

[RoryA]

The issue is that all the items in the combobox are stored as text. So when your UNIQUEVALUES routine tests the values in the sheet (which are numeric) against the criteria (which is text) none of them match. You then pass an empty variable (SortData) to the next routine, which it doesn't like.
Really you should be testing to see if SortData is Empty before you do anything fursther with it, but you should also add a check to see if the item selected from the list is a number (with IsNumeric) and then convert it accordingly (e.g. use Val or CDbl) and then use that converted value as the criterion.

 

[0123ren]

Ah brilliant, thanks very much for your help, it has been invaluable!!

​

 

[RoryA]

My pleasure.