Combolist populate Textbox help

[John McMullan]

Good morning,

I am using some code I borrowed from this very forum but unfortunately I cannot get it to work as I hoped and my lack of any knowledge has prompted me to ask for help.

This is the code for my sheet 'App'

Private Sub TextBox1_Change()
End Sub
Private Sub UserForm_Initialize()
    ComboBox1.List = Worksheets("App").Range("A2:A9").Value
End Sub
Private Sub ComboBox1_Change()
    TextBox1.Text = Application.VLookup(ComboBox1.Value, Worksheets("App").Range("B2:G10"), 2, False)
End Sub

I have a button and when pressed it opens the form as expected, with ComboBox1 and TextBox1 inside. Odd thing is if I select H006-006-048 is displays H001-003-016 in the TextBox1, which is the wrong row. Every other selection causes a Run-time error '13' Type Mismatch.

Below is a table on sheet 'App' I am pulling data from, blank cells are intentional.

A​	B​	C​	D​	E​	F​	G​
H001-005-012​	H006-006-048​	H001-003-016​	H040-001-030​	H001-003-014​
H004-001-021​	H001-002-001​	H001-005-019​	H004-008-005​	H004-001-001​	H004-001-022​	H004-003-001​
H006-006-048	H004-003-001	H001-005-012	H001-005-002	H001-003-016
H003-001-041	H003-004-001	H001-016-100	H001-016-001	H006-006-146
H032-014-005	H017-013-168
H006-009-029	None
H006-009-037	None
H017-018-400	H017-016-303
H006-009-029	H017-018-401	H001-016-002

<tbody>
</tbody>

When I choose an option from the ComboBox1 from A column I want it to display all the data in TextBox1 that is on that line, for example

H003-001-041 is selected and I want all the data H003-004-001, H001-016-100, H001-016-001 and H006-006-146.

Could anyone have a look and see what I may have done wrong. I hope I have provided enough information and explanation.

Regards,

John.

 

[Jon von der Heyden]

Hi John

I think the problem is that Vlookup doesn't work the way that you expect.

You are looking for a lookup value that does not exist in column B. I.e. your left-most column of your lookup table is column B, and this is where Vlookup will attempt to match the lookup value.

So you need to lookup in column A, and return a result from column B. If you want to return multiple results then you need to concatenate the results of an array vlookup. I recommend using the Excel worksheet function.

Private Sub UserForm_Initialize()
    ComboBox1.List = Worksheets("App").Range("A2:A9").Value
End Sub
Private Sub ComboBox1_Change()
    TextBox1.Value = Join$(Evaluate("VLOOKUP(""" & ComboBox1.Value & """,A:G,{2,3,4,5,6,7},FALSE)"), ", ") 'Application.VLookup(ComboBox1.Value, Worksheets("App").Range("B2:G10"), 2, False)
End Sub

 

[John McMullan]

Hi Jon,

The code works perfectly, thank you so much and thank you for the lookup explanation I understand now why it could not fetch the information.

I do seem to have left out one important piece of information it seems. The button is on a separate sheet 'Docs'. If I activate the button it gives a runtime error. If I switch to sheet 'App' and run the code it works perfectly. I wish the 'App' sheet to be hidden so it cannot be edited. Can I add something to allow this?

Also, the blank fields are showing in results, example H032-014-004, H017-013-168, , , ,

Can these empty fields be omitted from the results?When this document is complete it could be a very long line, this is a sample of data.

Many thanks,

John.

 

[Jon von der Heyden]

Hi

This is a bit crude, but it should work:

Private Sub UserForm_Initialize()
    ComboBox1.List = Worksheets("App").Range("A2:A9").Value
End Sub
Private Sub ComboBox1_Change()
    Dim varResult   As Variant
    
    varResult = Evaluate("VLOOKUP(""" & ComboBox1.Value & """,App!A:G,{2,3,4,5,6,7},FALSE)")
    
    If Not IsError(varResult) Then
        varResult = Replace(Join$(varResult, ","), ",,,", "")
        varResult = Replace(varResult, ",,", "")
        If Right$(varResult, 1) = "," Then varResult = Left$(varResult, Len(varResult) - 1)
        
        TextBox1.Value = varResult
    End If
End Sub

 

[John McMullan]

Hi Jon,

Thank you once again, I really appreciate you help and time you have given me.

Regards,

John.