General advice for a code i have in use

[ipbr21054]

Morning,
I have this code in use.
I have also supplied a screen shot after i type a value to search for.

My question is that i see no pattern for any column shown when its looking for the value i enter.
I mean in this case i entered SUBARU so if the code started at say row 1 then went down all the rows looking for the typed value then surely the rown column would increase like 1, 57, 168, 221, 500 etc but as you can see each coloumn is just all over the place.

Private Sub ClearButton_Click()
TextBox1.Value = ""
TextBox1.SetFocus
ListBox1.Clear
End Sub
Private Sub CloseButton_Click()
Unload PostageItemSoldSearch
End Sub

Private Sub ListBox1_Click()
  Set sh = Sheets("POSTAGE")
  sh.Select
  Range("C" & ListBox1.List(ListBox1.ListIndex, 3)).Select
  Unload PostageItemSoldSearch
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("POSTAGE")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 6
    .ColumnWidths = "240;100;250;50;150;100"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("C8", Range("C" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.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                 'Item
              .List(i, 1) = f.Offset(, -2).Value  'Date
              .List(i, 3) = f.Row                 'Row Number
              .List(i, 2) = f.Offset(, -1).Value  'Customers Name
              .List(i, 4) = f.Offset(, 6).Value   'Ebay User Name
              .List(i, 5) = f.Offset(, 1).Value   'Info

              added = True
              Exit For
          End Select
        Next
        If added = False Then
              .AddItem f.Value                                 'Item
              .List(.ListCount - 1, 1) = f.Offset(, -2).Value  'Date
              .List(.ListCount - 1, 3) = f.Row                 'Row Number
              .List(.ListCount - 1, 2) = f.Offset(, -1).Value  'Customer Name
              .List(.ListCount - 1, 4) = f.Offset(, 6).Value  'Ebay User Name
              .List(.ListCount - 1, 5) = f.Offset(, 1).Value  'Info
              
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBox1 = UCase(TextBox1)
      .TopIndex = 0
    Else
      MsgBox "NO SOLD ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET SOLD ITEM SEARCH"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
  End If
End Sub

 

[ipbr21054]

No it does not work.
I dont see key down ??

I type SUBARU then hit enter but then nothing is shown in listbox etc

 

[dmt32]

Works fine for me
There is no keydown event - The code is called from textbox1 change event as you type
The search columns are B C & I if SUBARU does not exist in any of those columns, nothing will be returned? The search columns can be changed if needed

Dave

 

[ipbr21054]

My mistake.
I was using Subaru on the dummy sheet and not my full sheet.
Also swapped around B C I & works fine.
If there’s many of the same item I’ve typed there is some lag but I assume that’s to be expected.

 

[dmt32]

You will get some lag on large worksheet which is why the columns being searched are specified in code but in general, should work ok.

Dave