Listbox on userform to show results in reverse order

[ipbr21054]

I have a userform where the user types a customers name in a Textbox.
Using a command button the code searches for that typed value & then lists the results in the Listbox.
The results are shown as per screenshot where you can see from day 1 a low row number & also an old date.
The user is then at present having to scroll for a while to see newer results.

My question is can the results be reversed so as opposed older values at the top then scroll to new have it so new values are at the top then scroll to older results.
Maybe the search is looking from Row 7 down the page to todays current row which is 2312 when maybe we can alter it to search from last current row with values up to row 7,just a thought/

The code for the Listbox is supplied below.

Private Sub CommandButton2_Click()
      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 = 4
        .ColumnWidths = "220;130;160;10"
        If TextBox8.Value = "" Then Exit Sub
        Set r = Range("B8", Range("B" & Rows.count).End(xlUp))
        Set f = r.Find(TextBox8.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                 'col B
                  .List(i, 1) = f.Offset(, 2).Value   'col D
                  .List(i, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
                  .List(i, 3) = f.Row                 'row
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, 2).Value   'col D
              .List(.ListCount - 1, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
              .List(.ListCount - 1, 3) = f.Row                 'row
            End If
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> Cell
          TextBox8 = UCase(TextBox8)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
          TextBox8.Value = ""
          TextBox8.SetFocus
      End If
      End With
      
End Sub

 

[Domenic]

Try replacing...

Set f = r.Find(TextBox8.Value, LookIn:=xlValues, LookAt:=xlPart)

with

Set f = r.Find(TextBox8.Value, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)

Then replace...

Set f = r.FindNext(f)

with

Set f = r.FindPrevious(f)

Hope this helps!

 

[ipbr21054]

I tried that as advised but the Listbox still shows the same order

Private Sub CommandButton2_Click()
      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 = 4
        .ColumnWidths = "220;130;160;10"
        If TextBox8.Value = "" Then Exit Sub
        Set r = Range("B8", Range("B" & Rows.count).End(xlUp))
        Set f = r.Find(TextBox8.Value, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
        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                 'col B
                  .List(i, 1) = f.Offset(, 2).Value   'col D
                  .List(i, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
                  .List(i, 3) = f.Row                 'row
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, 2).Value   'col D
              .List(.ListCount - 1, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
              .List(.ListCount - 1, 3) = f.Row                 'row
            End If
            Set f = r.FindPrevious(f)
          Loop While Not f Is Nothing And f.Address <> Cell
          TextBox8 = UCase(TextBox8)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
          TextBox8.Value = ""
          TextBox8.SetFocus
      End If
      End With
      
End Sub

 

[NoSparks]

You need to use a simple Do-Loop along the lines of your post here

 

[Domenic]

Can you confirm that the data is sorted by date, and in ascending order, as per your image, regardless of the item?

Also, why are you using the AddItem method in two different ways?

 

[ipbr21054]

Wouldnt know how to confirm that but i can only go on what happens & what i see.
The search used above was for the same customer.

So if it helps i did a search for A ,see results to see how Listbox is then shown.
It looks like the Listbox is sorted from Customers name as the dtaes / row number are all over the place.

When values are sent from userform to worksheet they are proceeded by 001,002,003 etc
So looking down worksheet over various dates you would see them in that order so i think this is the way the Listbox is populated,Do you agree ?

Using the name KELVIN GOH in previous example the List would be populated as found & that would be in the order as it was put on the worksheet, Make sense ?

Not sure how to answer add item question,code was advised a while ago & been using it like that since.

 

[Fuji]

How I read your code is
1) Loop through top to bottom.
2) If duplicate, only first appeared row will in the list regardless of the date.

Now how do you want it?

Descending order by date?

Or Max date for each name by order of appearance of source data?

Or something else?

 

[ipbr21054]

My goal is the type a value in search box.
The code will first find last row with values then start to search up the sheep to row 7 which is the first row.
At present using the example search KELVIN GOH it shows 8, 34, 41, 83 , 90 BUT i need it in reverse so 90, 83, 41, 34, 8
There should be no duplicates in my sheet.
Not sure why you say max date, there should be no restrictions,just find the value to search for then list them in the Listbox in the order mentioned.
Row number is how it should be sorted.
My currectnt last row with values is 2313 & to my first row of which is row 7

Basically find the value and list as high row number to low row number.

 

[Fuji]

If no duplicate and from the bottom then something like

Private Sub CommandButton1_Click()
    Dim s$, i&, ws As Worksheet
    Me.ListBox1.Clear
    s = Me.TextBox8
    If s = "" Then Exit Sub
    Set ws = Sheets("POSTAGE")
    With Me.ListBox1
        .ColumnCount = 4
        .ColumnWidths = "220;130;160;10"
        For i = ws.Range("b" & Rows.Count).End(xlUp).Row To 7 Step -1
            If InStr(1, ws.Cells(i, "b"), s, 1) Then
                .AddItem ws.Cells(i, "b")
                .List(.ListCount - 1, 1) = ws.Cells(i, "d")
                .List(.ListCount - 1, 2) = ws.Cells(i, "f").Text
                .List(.ListCount - 1, 3) = i
            End If
        Next
    End With
End Sub

 

[ipbr21054]

Well that did the trick Thanks