General advice for a code i have in use

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
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.




Rich (BB code):
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
 

Attachments

  • 1991.jpg
    1991.jpg
    208.4 KB · Views: 13

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
No it does not work.
I dont see key down ??

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,669
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,669
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,909
Messages
5,678,513
Members
419,768
Latest member
eguechi09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top