CODEWITHSUNDEEP
excelcomboboxrangeuserformvba
dman
dman

Reputation: 57

Populating combobox with dynamic horizontal range

I’m needing VBA code that will look up a value from column ‘A’ and list the adjacent horizontal non blank cells to the right in a userform combox1.

Sample Data:

+-----+----+----+----+---+
|  A  | B  | C  | D  | E |
+-----+----+----+----+---+
|  A1 | 63 |    | 55 | 5 |
+-----+----+----+----+---+

Sudo Code:

Sub test()
myVal = “A1”
Findme = myVal
Set match = Sheets(“Sheets1”).Range(A:A).Find(Findme)
myRange = foundRange
Userform1.Combobox.value = myRange
Exit Sub

In the example code above, foundRange would be the row where the value was found, plus columns ‘B’ through ‘E’, minus any blanks.

Combobox Values:

63 55 56

Thank you!

Upvotes: 0

Views: 889

Answers (1)

danieltakeshi
danieltakeshi

Reputation: 939

Create a Userform named Userform1, ComboBox named ComboBox and Button named CommandButton1. Like on the image below:

UserForm

Then use this code on the commandButton to populate the ComboBox:

Private Sub CommandButton1_Click()
    Dim ws1 As Worksheet
    Dim i As Long, c As Long
    Dim rng As Range, rng2 As Range
    Dim cellFound As Range
    Dim lastrow As Long, lastcolumn As Long

Set ws1 = ThisWorkbook.Sheets(1)
Findme = "A1"
lastrow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
Set rng = ws1.Range("A:A") 'range to search
Set rng2 = rng(lastrow, 1)

        With rng
         Set cellFound = .Find(what:=Findme, After:=rng2, LookIn:=xlValues)
            If Not cellFound Is Nothing Then
                FirstAddress = cellFound.Address
                Do
                    lastcolumn = ws1.Cells(ws1.Range(cellFound.Address).Row, ws1.Columns.Count).End(xlToLeft).Column
                    For i = Range(cellFound.Address).Column + 1 To lastcolumn
                    If ws1.Cells(Range(cellFound.Address).Row, i) <> "" Then ComboBox.AddItem ws1.Cells(Range(cellFound.Address).Row, i).Value
                    Next i
                    Set cellFound = .FindNext(cellFound)
                Loop While Not cellFound Is Nothing And cellFound.Address <> FirstAddress
             End If
        End With
        
End Sub

This code uses the .Find function that you wanted to use to add item on the ComboBox ComboBox.AddItem. Note that the code is inside the Userform, otherwise some changes are necessary to turn it global.

After finding A1 you use its Address Row to obtain the last column. And loop through CellFound + 1Column (2 on the sample) to last column comparing if the value in different from blank. If it is different, then add to ComboBox.

lastcolumn = ws1.Cells(ws1.Range(cellFound.Address).Row, ws1.Columns.Count).End(xlToLeft).Column
For i = Range(cellFound.Address).Column + 1 To lastcolumn
            
If ws1.Cells(Range(cellFound.Address).Row, i) <> "" Then ComboBox.AddItem ws1.Cells(Range(cellFound.Address).Row, i).Value
Next i

Then Find next value and make the same thing, so the Column A can have more than one match.

Set cellFound = .FindNext(cellFound)

The FindMe value can be replaced by any value, such as TextBoxes or Cell.Values.

And the Range to Search rng can be set to the range you are using. e.g.: Your entire Worksheet

Upvotes: 1

Related Questions