Search for a value on worksheet even if there is a space involved ex HU83 or HU 83

[ipbr21054]

Hi,
I am using the code below
It works like this.

I type HU 83 & the results are populated into the ListBox.
BUT
If i type HU83 i am told NO SUCH KEY TYPE FOUND

Some values are in the database as both so can we edit the code supplied so the following will return a value in the Listbox.

HU101 or HU 101 SAME GOES FOR FO21 or FO 21
In the code below i used LookAt:=xlPart thinking it would return both entries but it didnt.

Private Sub TextBoxKeyType_Change()
Me.Label1.Visible = False
Me.Label2.Visible = False
Me.Label3.Visible = False
Me.Label4.Visible = False
TextBoxKeyType = UCase(TextBoxKeyType)
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("DATABASE")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "200;150;260;10"
   If TextBoxKeyType.Value = "" Then Exit Sub
    Set r = Range("C6", Range("C" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBoxKeyType.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.Offset(, -2).Value
              .List(i, 2) = f.Offset(, -1).Value
              .List(i, 3) = f.Row
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -2).Value
          .List(.ListCount - 1, 2) = f.Offset(, -1).Value
          .List(.ListCount - 1, 3) = 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 SUCH KEY TYPE FOUND", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
      TextBoxKeyType.Value = ""
      TextBoxKeyType.SetFocus
    End If
  End With
End Sub

 

[bobsan42]

Can't you just search and replace all spaces in column C with "". Then search.
And make sure no spaces get entered there.
You can use wild cards or RegExp but it is better to have consistent data than to waste time looking for workarounds.