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

 

[Dan_W]

Hi. I had a look at the screen capture and the code. I suppose if there is a pattern, it's that the first column is ordered alphabetically, and so the row numbers between Subaru FOB and Subary KEY will look out of order. The row numbers for each grouping are ordered within their groups on a descending basis. Is that what you're asking? Or are you asking why is it ordered on a descending basis?

 

[ipbr21054]

Morning.
I see what you are saying.
I assumed / thought that what was searched for word start searching at column 1 then when found list them in row order.
So the code would start looking at row 1 then as the search went down each row & once found list it for me.
Hence like 1, 57, 299, 345 etc.
I don’t even know how the code is searched to just put it in a random order.

 

[dmt32]

I assumed / thought that what was searched for word start searching at column 1 then when found list them in row order.

Your code is searching Column 3 (C)

Set r = Range("C8", Range("C" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)

can you post copy of the worksheet using MrExcel Addin?

Dave

 

[ipbr21054]

Hmmm maybe why it doesnt work then.
I thought search column C for my typed word in this case SUBARU then once the list starts to populate have it shown in column order.

Ive just got it to look for typed word.

How can i achieve both

 

[dmt32]

Hmmm maybe why it doesnt work then.
I thought search column C for my typed word in this case SUBARU then once the list starts to populate have it shown in column order.

Ive just got it to look for typed word.

How can i achieve both

Possible to do a multicolumn search but posting copy of your worksheet with dummy data if needed using MrExcel Addin always helpful to forum

Dave

 

[ipbr21054]

Here we go.

Dummy Sheet

 

[dmt32]

Give this a try

Replace ALL your forms code with following

Dim sh As Worksheet
Private Sub ClearButton_Click()
    TextBox1.Value = ""
    TextBox1.SetFocus
End Sub
Private Sub CloseButton_Click()
    Unload Me
End Sub
Private Sub ListBox1_Click()
    With Me.ListBox1
      sh.Range("C" & .List(.ListIndex, 3)).Select
    End With
    Unload Me
End Sub
Private Sub TextBox1_Change()
    SearchMatches sh, Me, Me.TextBox1.Text
End Sub

Private Sub UserForm_Initialize()
    Set sh = ThisWorkbook.Worksheets("POSTAGE")
    sh.Select
    With Me.ListBox1
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "240;100;250;50;150;100"
    End With
End Sub

Note variable sh which must sit at the TOP of your forms code page OUTSIDE any procedure.


Place this code either in a STANDARD module or your forms code page

Sub SearchMatches(ByVal sh As Object, ByVal Form As Object, ByVal Search As String)
    Dim r               As Long, c As Long, LastRow As Long
    Dim SearchColumns   As Variant, arr As Variant
  
    LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
  
    arr = sh.Cells(8, 1).Resize(LastRow, 11).Value
  
    'search columns(B, C, I)
    SearchColumns = Array(2, 3, 9)
  
    With Form.ListBox1
        .Clear
        If Len(Search) = 0 Then Exit Sub
        For r = 1 To UBound(arr, 1)
            For c = 1 To UBound(arr, 2)
                If Not IsError(Application.Match(c, SearchColumns, 0)) Then
                    If UCase(arr(r, c)) Like "*" & UCase(Search) & "*" Then
                        .AddItem arr(r, 3)                    'Item
                        .List(.ListCount - 1, 1) = arr(r, 1)  'Date
                        .List(.ListCount - 1, 3) = r          'Row Number
                        .List(.ListCount - 1, 2) = arr(r, 2)  'Customers Name
                        .List(.ListCount - 1, 4) = arr(r, 9)  'Ebay User Name
                        .List(.ListCount - 1, 5) = arr(r, 4)  'Info
                        Exit For
                    End If
                End If
            Next c
        Next r
    End With
End Sub

The code should search for matches as you type in columns B, C & I

Dave

 

[ipbr21054]

Not sure if i followed the last code correctly.
This is all on the forms code

Dim sh As Worksheet
Private Sub ClearButton_Click()
    TextBox1.Value = ""
    TextBox1.SetFocus
End Sub
Private Sub CloseButton_Click()
    Unload Me
End Sub
Private Sub ListBox1_Click()
    With Me.ListBox1
      sh.Range("C" & .List(.ListIndex, 3)).Select
    End With
    Unload Me
End Sub
Private Sub TextBox1_Change()
    SearchMatches sh, Me, Me.TextBox1.Text
End Sub

Private Sub UserForm_Initialize()
    Set sh = ThisWorkbook.Worksheets("POSTAGE")
    sh.Select
    With Me.ListBox1
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "240;100;250;50;150;100"
    End With
End Sub
Private Sub SearchMatches(ByVal sh As Object, ByVal Form As Object, ByVal Search As String)
    Dim r               As Long, c As Long, LastRow As Long
    Dim SearchColumns   As Variant, arr As Variant
  
    LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
  
    arr = sh.Cells(8, 1).Resize(LastRow, 11).Value
  
    'search columns(B, C, I)
    SearchColumns = Array(2, 3, 9)
  
    With Form.ListBox1
        .Clear
        If Len(Search) = 0 Then Exit Sub
        For r = 1 To UBound(arr, 1)
            For c = 1 To UBound(arr, 2)
                If Not IsError(Application.Match(c, SearchColumns, 0)) Then
                    If UCase(arr(r, c)) Like "*" & UCase(Search) & "*" Then
                        .AddItem arr(r, 3)                    'Item
                        .List(.ListCount - 1, 1) = arr(r, 1)  'Date
                        .List(.ListCount - 1, 3) = r          'Row Number
                        .List(.ListCount - 1, 2) = arr(r, 2)  'Customers Name
                        .List(.ListCount - 1, 4) = arr(r, 9)  'Ebay User Name
                        .List(.ListCount - 1, 5) = arr(r, 4)  'Info
                        Exit For
                    End If
                End If
            Next c
        Next r
    End With
End Sub

 

[dmt32]

You can place SearchMatches code in a standard module if wanted but forms code page should be ok

You have not said if code works?