Advice for searchbox but with bullet options

[ipbr21054]

Hi,
I currently use a code which looks only in column C but now i need to also be able to look in column D
I though that we could use the Option Buttons for selecting which column to use.
Something like,
If Option Button 1 Then look in column C
If Option Button 2 Then look in column D

Here is the code i think you need.

Many Thanks.

Private Sub TextBoxSearch_Change()
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("POSTAGE")
  sh.Select
  With ListBoxSearch
    .Clear
    .ColumnCount = 2
    .ColumnWidths = "100;0"
    If TextBoxSearch.Value = "" Then Exit Sub
    Set r = Range("C9", Range("C" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBoxSearch.Value, LookIn:=xlValues, LookAt:=xlPart)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
              .List(i, 1) = f.Row
              added = True
              Exit For
          End Select
        Next
           If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Row
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBoxSearch = UCase(TextBoxSearch)
      .TopIndex = 0
      Else
      MsgBox "NO ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET ITEM SEARCH"
      TextBoxSearch.Value = ""
      TextBoxSearch.SetFocus
    End If
  End With
End Sub

 

[ipbr21054]

Sorry forgot to add my attempt.
I see end with without with ?

Private Sub TextBoxSearch_Change()
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("POSTAGE")
  sh.Select
  With ListBoxSearch
    .Clear
    .ColumnCount = 2
    .ColumnWidths = "100;0"
    If TextBoxSearch.Value = "" Then Exit Sub
    If OptionButton1.Value = True Then
    Set r = Range("C9", Range("C" & Rows.Count).End(xlUp))
    Else
    Set r = Range("D9", Range("D" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBoxSearch.Value, LookIn:=xlValues, LookAt:=xlPart)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
              .List(i, 1) = f.Row
              added = True
              Exit For
          End Select
        Next
           If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Row
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBoxSearch = UCase(TextBoxSearch)
      .TopIndex = 0
      Else
      MsgBox "NO ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET ITEM SEARCH"
      TextBoxSearch.Value = ""
      TextBoxSearch.SetFocus
    End If
  End With
End Sub

 

[ipbr21054]

Ive now managed to sort the above but stuck with actually making the change on the worksheet.
This is the code i have.

Example of what my form should do.
Search column C or D depending on selected Option Button
Example C
Search for say DOG
I then need to look for all DOG values in column C
Then change fo CAT
BUT i get a message saying NO DOG FOUND
I can see plenty hence the code is incorrect.

Please advise what i did wrong
Thansk

Private Sub MakeTheChangeButton_Click()
    Dim lRow As Long, Rng As Range, f As Range

    With Worksheets("POSTAGE")
        If OptionButton1.Value = True Then
        lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        Set r = Range("C8", Range("C" & Rows.Count).End(xlUp))
    Else
        lRow = .Cells(.Rows.Count, 4).End(xlUp).Row
        Set r = Range("D8", Range("D" & Rows.Count).End(xlUp))
        Set Rng = .Range("C8:C" & lRow)
        Set f = Rng.Find(TextBoxFind.Text, , xlValues, xlWhole)
        If f Is Nothing Then
            MsgBox TextBoxFind.Text & " WAS NOT FOUND, PLEASE TRY AGAIN", vbCritical, "UPDATE VEHICLE INFO MESSAGE"
             
        Else
            Rng.Replace What:=TextBoxFind.Text, Replacement:=TextBoxReplace.Text, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
             Application.ScreenUpdating = True
            .Range("C8:C" & lRow).HorizontalAlignment = xlCenter
            MsgBox "WORKSHEET UPDATED SUCCESSFULLY", vbInformation, "UPDATE VEHICLE INFO MESSAGE"

        End If
        TextBoxFind.Value = ""
        TextBoxReplace.Value = ""
        TextBoxSearch.Value = ""
        TextBoxSearch.SetFocus
        End If
    End With
    
End Sub

 

[ipbr21054]

Some infor to assist you.
I have done the F8 process and watched what happens.
I see the yellow code as shown in screen shot attached
The next press of F8 it jumps to End If then End With ???

As you can see there is column C with HIRE HB1 and i have searched for anything with H

But i am told nothing found ?

 

[ipbr21054]

Now completed