Sorting advise for letters & number value

[ipbr21054]

Evening,
Is there a different code when sorting letters that also contain numbers as opposed to just letters on there own ?

I ask becuase if i type in the correct TextBox the word HONDA i see the list sorts by customers name which is correct,so in my example ALEX 1, 2, 3, 4

But the issue i see if i type in the correct TextBox the word XL i see the list sorts in the column shown XL 700 1, 2, 3, 4 & the customers name is unsorted.
I assume if i type XL i expect to still see the customers name to be sorted NOT the XL part


Screen shots supplied to assist

Private Sub TextBoxMODEL_Change()
TextBoxMODEL = UCase(TextBoxMODEL)
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("DETAILS")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "0;180;150;150;100;80;60"
    If TextBoxMODEL.Value = "" Then Exit Sub
    Set r = Range("C2", Range("C" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBoxMODEL.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.Offset(, 0).Value
              .List(i, 4) = f.Offset(, 1).Value
              .List(i, 5) = f.Offset(, 4).Value
              .List(i, 6) = f.Offset(, 5).Value
              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.Offset(, 0).Value
          .List(.ListCount - 1, 4) = f.Offset(, 1).Value
          .List(.ListCount - 1, 5) = f.Offset(, 4).Value
          .List(.ListCount - 1, 6) = f.Offset(, 5).Value
        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 MODEL WAS FOUND", vbCritical, "CLONING INFORMATION MESSAGE"
      TextBoxMODEL.Value = ""
      TextBoxMODEL.SetFocus
    End If
  End With
End Sub

 

[mackc557]

Select Case StrComp(.List(i), f.Value, vbTextCompare)

It looks like you are comparing the value of the search to the item in the list for your sort. If you wanted to sort by customer you would likely need to change

f.Value

to

f.Offset(,-2).Value

This should compare Customer to Customer.

 

[ipbr21054]

What I’m trying to do is,
Search for a model but then sort that model by customers name.

So I search for XL but then the results should be sorted for customers.

The same process works for my other text box searches but these are all letters where model is letters & numbers.

 

[ipbr21054]

It looks like you are comparing the value of the search to the item in the list for your sort. If you wanted to sort by customer you would likely need to change

f.Value

to

f.Offset(,-2).Value

This should compare Customer to Customer.

Hi,
Ive changed the code as advised & now i see it sort the customers names correctly "in this case" See screen shot
So i searched for XL & the results were loaded into the listbox & were sorted by customers name, Alex 1, 2 , 3, 4 this is correct.

However just checking another when i searched for CB the results were loaded into the listbox but the customers names we not sorted correct.
See screen shots.

Also the same for when i search CBF

Why isnt the code following the same path each time.

 

[mackc557]

Do you mind showing what your source data sheet looks like? Also are you using three different methods (subs) for filtering, one for each filter box? If so it might be beneficial to create a single method such as:

Sub FilterDataBy(FilterBy as String, SortBy as String)

Add a combobox for sorting onto your page. Then have a method call in each of your textbox_change events like:

Call FilterDataBy("Model",SortBy_ComboBox.Value)

Make sure you set up your method to accept and use those variables properly, and you'll have one method that is flexible enough to filter and sort by a few things.

 

[ipbr21054]

Hi,
I will add screen shot shortly but this information needs really not to be seen by everybody.
Some info for you.

Userform has 3 separate textboxes.
One list box.
Each textbox allows me to search the database in a specific column.
So I search that specific column & results are then placed in the listbox but I have the list sorted in the first column by customers name.
No point doing all this then looking up & down for a while looking for the name hence what it’s done like this to complete it quick & correct.

Thanks for the help so far.

 

[ipbr21054]

Screenshot of database attached,names etc changed.

Screenshot of userform also attached

One thing to note for sorting Model is if i search for CB the list is populated with models in this order,

CB 1000
CB 1300
CB 600 N
CB 900 F

The customers are unsorted.

The CB should be the unsorted part & the customers names should be sorted just like when i use the same code for the other two searches.