populate last value in textbox for duplicates items across sheets based on selected combobox

[Maklil]

Hi

I try to brings last values for duplicates items across sheets if I select combobox1, optionbutton1 then should populate last value for duplicates item

here I have sheets (BRANDS, SS) should brings the last value for duplicate item in sheet SS even if there is not duplicates then should brings last value for the item the values should populate in textbox1 based on selected optionbutton1,combobox1 from column F for two sheets . the code somtimes works and somtimes brings the last value for sheet BRANDS and ignore sheet SS .

Private Sub ComboBox1_Change()
    Dim c       As Range, rng As Range
    Dim search  As String
    Dim ws As Variant
    ws = Array("BRANDS", "SS")

    Set rng = 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 ws 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 Me.OptionButton1.Value = True Then
            Me.TextBox2.Value = c.Offset(, 4).Value
        ElseIf Me.OptionButton2.Value = True Then
            Me.TextBox2.Value = c.Offset(, 5).Value
            
        End If
        
        
       
    End If
    
 Next

If ComboBox1.Value <> "" Then
TextBox1.Value = 1
End If
End Sub
Private Sub UserForm_Activate()
ComboBox1.RowSource = "BRANDS!" & Sheets("BRANDS").Range("B2", Sheets("BRANDS").Range("B65536").End(xlUp)).Address
ComboBox2.RowSource = "BRANDS!" & Sheets("BRANDS").Range("C2", Sheets("BRANDS").Range("C65536").End(xlUp)).Address
ComboBox3.RowSource = "BRANDS!" & Sheets("BRANDS").Range("D2", Sheets("BRANDS").Range("D65536").End(xlUp)).Address
ComboBox4.RowSource = "BRANDS!" & Sheets("BRANDS").Range("E2", Sheets("BRANDS").Range("E65536").End(xlUp)).Address

End Sub

Private Sub OptionButton1_Click()
    If Me.OptionButton1 Then
        If Me.ComboBox1.ListIndex > -1 Then ComboBox1_Change
    End If
End Sub

Private Sub OptionButton2_Click()
    If Me.OptionButton2 Then
        If Me.ComboBox1.ListIndex > -1 Then ComboBox1_Change
    End If
End Sub

as to optionbutton 2 is much complicated because the values are not existed in the same location for sheet BARNDS ,ASD .
when select the values in columns G for sheet BRANDS , column F for sheet ASD
combobox1, optionbutton2 ,then should populate last value for duplicates item
here I have sheets (BRANDS, AS) should brings the last value for duplicate item in sheet AS even if there is not duplicates , then should brings last value for the item . the values should populate in textbox1 based on selected optionbutton2,combobox1. so far I don't find idea to overcome this case
notic: when search the item should search for two sheets together based on combobox1,optionbutton1 or optionbutton 2
thanks