I need an expert help after trying a lot without success, please.
I have a price list in Sheet1 with 3 columns:
Medical Procedure
Type
Value of Procedure
In a userform, I need to return in Textbox1 the value of the procedure based on the criteria selected in combobox1 (with values that can be found in Medical Procedure column in Sheet1) and the caption in label1 (which already is populated with a value that can be encounter in the Type column in Sheet1).
I tried this found here in stackoverflow from the user B Hart (thanks, B Hart!), but I wasn't able to change it to return in a textbox as a numerical value (this vba insert the found value in a listbox instead). Another issue is that the criteria below is in two combobox. I need the two criteria to be in a combobox and another in a label.
Private Sub GetCondStrandValue()
Dim iRow As Long
Dim strValue As String
strValue = vbNullString
If Me.ComboBox1.Value = vbNullString Or Me.ComboBox2.Value = vbNullString Then Exit Sub
With Planilha1
For iRow = 2 To .Range("A65536").End(xlUp).Row
If StrComp(.Cells(iRow, 1).Value, Me.ComboBox1.Value, 1) = 0 And _
StrComp(.Cells(iRow, 2).Value, Me.ComboBox2.Value, 1) = 0 Then
strValue = .Cells(iRow, 3).Value
Exit For
End If
Next
End With
If strValue = vbNullString Then Exit Sub
With Me.ListBox1
'If you only want a single value in the listbox un-comment the .clear line
'Otherwise, values will continue to be added
'.Clear
.AddItem strValue
.Value = strValue
.SetFocus
End With
End Sub
Maybe something like this:
Private Sub combobox1_Change()
Dim lastRow As Integer
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Me
For r = 2 To lastRow
If Sheets("Sheet1").Cells(r, 1) = .ComboBox1.Value And Sheets("Sheet1").Cells(r, 2) = .Label1.Caption Then
.TextBox1.Text = Sheets("Sheet1").Cells(r, 3)
Exit For
End If
Next
End With
End Sub