Populate a combobox from a variable sheet and variable range

[LGRL20]

Hi everyone,

Ive been struggling with this for 2 days now I could really use some help.

I have the following problem:

Ive got a sheet1 named "initial" in wich ive got a bunch of buttons that open up different userforms, before the user is able to press the buttons he must choose between 3 options, warehouse1, warehouse2, warehouse 3. Once his chosen either of this he can press any button that will show a userform.
One of the buttons opens Userform1, in which the user must type an IDNUMBER, this userform has a combobox that has to be populated with variable information depending on 2 things:
- The warehouse the user selected before clicking the button
- And the IDNumber the user typed

Depending on the warehouse he chosed in "initial" it will take the information from either of the following sheets:
"Batch numbers 1st", "Batch numbers 2nd", "Batch numbers 3rd".
Depending on what he types on the textbox, the combobox should populate from the range
with row= 2 through row="finalrow" and from column="initialcolumn".

I have already written the code to find every variable, however when i get the code to run, the combobox is empty.
BUT, when I replace the underlined part of the code with a normal range lets say "B4:B11", the code runs ok.
Problem is that the range will always be variable depending on what the user types in the textbox.



Do
column = column + 1
Loop Until Worksheets(WSnumber).Cells(1, column) = TextBox2


row = 1
Do
row = row + 1


Loop Until Worksheets(WSnumber).Cells(row + 1, column) = Empty




ComboBox1.List = Sheets("Batch Numbers " & Cells(3, 2)).Range(Cells(2, column), Cells(row, column)).Value

If i change that part to this:

ComboBox1.List = Sheets("Batch Numbers " & Cells(3, 2)).Range("B4:B11").Value

the code runs just fine, how ever i need the range to be variable.

Please ill appreciate any help, ive been stucked for 2 days now.

 

[dmt32]

Hi welcome to the board.

Not sure I have correctly followed what you are trying to do but see if this update to your code helps:

Dim FindColumn As Range
Dim vList As Variant


Set FindColumn = Worksheets(wsnumber).Rows(1).Find(TextBox2.Text, LookIn:=xlValues, lookat:=xlWhole)


If Not FindColumn Is Nothing Then
    With Worksheets("Batch Numbers " & wsnumber)
        vList = .Range(.Cells(2, FindColumn.Column), .Cells(.Cells(.Rows.Count, _
        FindColumn.Column).End(xlUp).Row, FindColumn.Column)).Value
    End With


    ComboBox.List = vList


Else


    MsgBox TextBox2.Text & Chr(10) & "Record Not Found", 48, "Not Found"


End If

Solution is not tested & you will need to adjust to meet you specific project need.

Dave

 

[LGRL20]

Dear DMT,

Thanks a lot for your reply. It worked!
Only one issue now, whenever it finds a column that matches the value from textbox2, if that column is empty below the the matching cell, it shouldnt populate the combobox, however rightnow it populates the value from textbox2 and an empty cell. Anyway i can fix that? Thanks a lot in advance.

Update:

It also doesnt populate if it only has one value under the cell that was matched with textbox 2. It works perfectly with two or more values.
Thanks again.

greetings

 

[dmt32]

Dear DMT,

Thanks a lot for your reply. It worked!
Only one issue now, whenever it finds a column that matches the value from textbox2, if that column is empty below the the matching cell, it shouldnt populate the combobox, however rightnow it populates the value from textbox2 and an empty cell. Anyway i can fix that? Thanks a lot in advance.

Update:

It also doesnt populate if it only has one value under the cell that was matched with textbox 2. It works perfectly with two or more values.
Thanks again.

greetings

Hi,
see if these changes help:

Dim FindColumn As Range
Dim vList As Variant
Dim LastRow As Long


Set FindColumn = Worksheets(wsnumber).Rows(1).Find(TextBox2.Text, LookIn:=xlValues, lookat:=xlWhole)


If Not FindColumn Is Nothing Then
    With Worksheets("Batch Numbers " & wsnumber)
        LastRow = .Cells(.Rows.Count, FindColumn.Column).End(xlUp).Row
        If LastRow < 2 Then
            vList = ""
            MsgBox "No Records For " & TextBox2.Text, 48, "No Records"
        Else
            vList = .Range(.Cells(2, FindColumn.Column), .Cells(LastRow, FindColumn.Column)).Value
        End If
    End With
    
    If Not IsArray(vList) Then vList = Array(vList)
    
    
    ComboBox.List = vList


Else


    MsgBox TextBox2.Text & Chr(10) & "Record Not Found", 48, "Not Found"


End If

Dave

 

[LGRL20]

Dear DMT,

THANK YOU MAN! it worked perfectly.

Thanks a lot for the opportune time and the great answers.

Take care.