Populating combobox

[ccordner]

I have a short snippet of code that somebody else gave me to populate a combobox using unique values from a column.

With Sheets("Locations (Time Sheet)").Range("E2:E1048576")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then cboEmployeeHours.List = Application.Transpose(.keys)
End With

However the values are times and are coming back as serial numbers. I would like to format them as times, so I tried changing it to:

If Not .exists(e) Then .Add Format(e, "h:mm"), Nothing

Unfortunately, this throws up an error and I'm not sure why!

(Run-time error '457': This key is already associated wiht an element of this collection).

I also want to know, instead of specifying the last cell on the sheet, is there a way of specifying the last non-blank cell?

Thanks
Chris

 

[John_w]

The error occurs because that line is checking for the existence of time 'e' as a decimal number (Double data type), but adding it to the dictionary as a "h:mm" string. So the former never exists in the dictionary, but the latter does and the .Add error occurs when it tries to add a duplicate time.

Try this:

If Not .exists(Format(e, "h:mm")) Then .Add Format(e, "h:mm"), Nothing

For the last non-blank cell:

Dim lastRow As Long
With Sheets("Locations (Time Sheet)")
    lastRow = .Cells(Rows.Count, "E").End(xlUp).Row
    v = .Range("E2:E" & lastRow).Value
End With

 

[ccordner]

Thanks for that, it works perfectly.

Is there an easy way to adapt that to sort it? Or would I have to revert to going through the list and transferring the values into the combobox individually?

Thanks
Chris

 

[John_w]

You could try the dictionary sort routine at http://support.microsoft.com/kb/246067. It would probably be quite fast for a few hundred rows, but slow for a thousands of rows. How many rows are we talking about?

Or you could ditch the dictionary technique altogether and use Advanced Filter, filtering unique records to another (hidden) column, followed by an Excel Sort instead. Use the Macro Recorder for these manual steps to generate the VBA code.

 

[ccordner]

With this statement:

"v = .Range("E2:E" & lastRow).Value"

Is there a way of taking, say, the left four characters of the value? Ultimately I want to actually take the first word of the cell's value, using instr and left.

 

[John_w]

With this statement:

"v = .Range("E2:E" & lastRow).Value"

Is there a way of taking, say, the left four characters of the value?

Not directly in one statement, AFAIK. You would have to loop through the 'v' array, applying Instr and Left to each element to amend it as required.

 

[ccordner]

How do you do that? Something like?

For each z in v
    z=left(z, instr(z," "))

Chris

 

[John_w]

That's it. Here's a complete working example:

Sub pop()

    Dim ColumnEValues As Variant, EValue As Variant
    Dim lastRow As Long
    
    lastRow = 10
    
    ColumnEValues = Sheet1.Range("E2:E" & lastRow).Value
    
    For Each EValue In ColumnEValues
        Debug.Print "Before: " & EValue
        EValue = Left(EValue, InStr(EValue, " "))
        Debug.Print "After:  " & EValue
    Next
    
End Sub