Selected Answer
Maklil
Since your column number can vary, you can solve this by using a function (private to the UserForm) as below:
Private Function FindColumn(OutStr As String, WKS As String)
'reset output
FindColumn = 0
'find match
For p = 1 To 10
If Worksheets(WKS).Cells(1, p).Value = OutStr Then FindColumn = p
Next p
End Function
It has input values OutStr = PCR or PRICE (from the OptionButtons- see below) and WKS =the sheet name. These are passed to it from your ComboBox1_Change sub. It returns the column number matching the option button caption (or 0 is that is not found in the sheet).
Rather than hard-coding PRICE and PCR, I've relied on the captions on the option button to set both Label5 and what is looked for in row 1 of the searched sheets to find the column (so if the caption text of OptionButton1 is changed from "PRICE" to PRICE ($)" say, then the form and results will use that new value if OptionButton1. is clicked).
The ComboBox1_Change code is modified so the column containing PCR or PRICE is determined as OutCol once per sheet (at the start of the loop) then written to TextBox 5 by the line:
If OutCol > 2 Then Me.TextBox2.Value = C.Offset(, OutCol - 2).Value
where the >2 test prevents picking cells which don't exist (column <0).
The modified sub in the attached revised file and shown below (changes in bold):
Private Sub ComboBox1_Change()
Dim C As Range, rng As Range
Dim search As String
Dim ws As Variant, n As Long
Dim Cll As Range, OutCol As Long
'rewrite label 5
If Me.OptionButton1.Value = True Then Me.Label15.Caption = OptionButton1.Caption
If Me.OptionButton2.Value = True Then Me.Label15.Caption = OptionButton2.Caption
ws = Array("sheet1", "sheet2", "sheet3")
'loop through worksheets
For n = LBound(ws) To UBound(ws)
' find PCR or PRICE column (if any) = OutCol
If Me.OptionButton1.Value = True Then
OutCol = FindColumn(OptionButton1.Caption, CStr(ws(n)))
ElseIf Me.OptionButton2.Value = True Then
OutCol = FindColumn(OptionButton2.Caption, CStr(ws(n)))
Else
OutCol = 0
End If
' look in worksheet
Set rng = Worksheets(ws(n)).Range("B2:B12")
search = Me.ComboBox1.Value
Set C = rng.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False, _
SearchFormat:=False)
For Each Cll In rng
If Not C Is Nothing Then
'search value found
Me.ComboBox2.Value = C.Offset(, 1).Value
Me.ComboBox3.Value = C.Offset(, 2).Value
Me.ComboBox4.Value = C.Offset(, 3).Value
If OutCol > 2 Then Me.TextBox2.Value = C.Offset(, OutCol - 2).Value
End If
Next Cll
Next n
End Sub
Note that I've now corrected the reference to PCR in the UserForm (was PRC in the previous answer)
Hope this works well for you.
