ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- 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.
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