Combobox that populates text boxes on userform

[joereed11]

Hello

Any help is great. I have a userform that uses two comboboxes in order
to pull in data from that associated row. For example, my worksheet
has multiple rows (which change constantly see below).

What i'm looking for; combobox1 selects Audi, combobox2 selects Blue,
I want the associated values 30000 and 4 door each pulled and put into
a txtbox on my userform. Thanks for your help.


example

cell 1 2 3 4
row a Audi Blue 30000 4 door
row b Audi Red 45000 2 door

 

[Dave Peterson]

This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()

Dim myCol1 As Range
Dim myCol2 As Range
Dim Res As Variant
Dim myFormula As String
Dim myStr As String

If Me.ComboBox1.Value = "" Then
Exit Sub
End If

If Me.ComboBox2.Value = "" Then
Exit Sub
End If

With Worksheets("sheet1")
Set myCol1 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set myCol2 = myCol1.Offset(0, 1)

myFormula = "match(1,(" _
& myCol1.Address & "=""" & Me.ComboBox1.Value & """)*(" _
& myCol2.Address & "=""" & Me.ComboBox2.Value & """),False)"

Res = .Evaluate(myFormula)

If IsError(Res) Then
myStr = "no match"
Else
myStr = Format(myCol1(Res).Offset(0, 2).Value, "$#,##0.00") _
& vbLf & myCol1(Res).Offset(0, 3).Value
End If

Me.TextBox1.Value = myStr
End With

End Sub
Private Sub UserForm_Initialize()

With Me.TextBox1
.MultiLine = True
.WordWrap = True
.Value = ""
End With

With Me.ComboBox1
.Style = fmStyleDropDownList
.AddItem "Audi"
.AddItem "Ford"
.AddItem "Chevy"
End With

With Me.ComboBox2
.Style = fmStyleDropDownList
.AddItem "Red"
.AddItem "Green"
.AddItem "Blue"
End With

End Sub