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
(E-Mail Removed) wrote:
>
> 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
Similar Threads
