figment222
New Member
- Joined
- Mar 6, 2015
- Messages
- 48
I am going bonkers trying to figure out why this is happening, so I'm hoping you experts can clue me in here. I'll apologize ahead, because I'm really not sure what the problem is. I'm usually pretty good about finding solutions from other threads, but this one has me in a pinch
I have a userform with a listbox that this macro is populating, based on search criteria found via textbox string and also some comboboxes. The comboboxes aren't giving me problems and neither was the textbox search string until today.
i have a sheet with "Records" and there are about 8 columns with info I want to bring in. One of them is a "Description" field. The user can type a string into the textbox and this will filter down to only those records where that string is found in the description field.
if it is found, then it puts the appropriate values fromt the 8 columns for that row into the listbox via .add item
at launch, I want the search string to be "*", so the user sees ALL records and can narrow them down via whatever criteria.
this means, it should start at row 2, and if it isn't blank, then add the item. findNext should let it see that row 3 isn't blank, either, so let's add that one, too. and so on...
for some reason, it has begun endlessly looping, rather than cycling through the list only once and adding only relevant records. here's the code. I'm sure it looks ugly, because I'm a novice, but after debugging and debugging and debugging some more, I think the culprit is the line towards the bottom where i tell it to "FindNext". It does NOT find the next one, but jumps down like 12 rows and just loops and loops.
I've tested a simpler version in another module and it seems to work alright, but for whatever reason, when I add the first item, it can no longer set the found range to the "find next" range. I'm so confused...
I have a userform with a listbox that this macro is populating, based on search criteria found via textbox string and also some comboboxes. The comboboxes aren't giving me problems and neither was the textbox search string until today.
i have a sheet with "Records" and there are about 8 columns with info I want to bring in. One of them is a "Description" field. The user can type a string into the textbox and this will filter down to only those records where that string is found in the description field.
if it is found, then it puts the appropriate values fromt the 8 columns for that row into the listbox via .add item
at launch, I want the search string to be "*", so the user sees ALL records and can narrow them down via whatever criteria.
this means, it should start at row 2, and if it isn't blank, then add the item. findNext should let it see that row 3 isn't blank, either, so let's add that one, too. and so on...
for some reason, it has begun endlessly looping, rather than cycling through the list only once and adding only relevant records. here's the code. I'm sure it looks ugly, because I'm a novice, but after debugging and debugging and debugging some more, I think the culprit is the line towards the bottom where i tell it to "FindNext". It does NOT find the next one, but jumps down like 12 rows and just loops and loops.
I've tested a simpler version in another module and it seems to work alright, but for whatever reason, when I add the first item, it can no longer set the found range to the "find next" range. I'm so confused...
Code:
Public Sub SearchButton()
Dim rFound As Range
Dim sAddr As String
Dim LastRow As Long: LastRow = Sheet3.Range("A1").CurrentRegion.Rows.Count
Dim strSearch As String
Dim Category As String
Dim Parameter As String
Dim Filter1, Filter2, Filter3, Filter4, Filter5 As Range
Dim TestFilter As Range
Dim Valid As Boolean: Valid = True
If IsUserFormLoaded("SearchRecords") = False Then
SearchRecords.Show vbModeless
'SearchRecords.TextBox1.Value = "43626"
'SearchRecords.ComboBox1.ListIndex = 2
'SearchRecords.ComboBox1_1.ListIndex = 1
End If
SearchRecords.ListBox1.Clear
'''''''
Set Filter5 = Sheet3.Range("1:1").Find(Parameter, Sheet3.Range("1:1").Find(Category)) ': Debug.Print Filter5
'''''
x = 0
For x = 1 To 5
If SearchRecords.Controls("Combobox" & x).Value <> "Category" Then
Category = SearchRecords.Controls("Combobox" & x).Value
If Category = "Misc" Then Category = "Settings"
If SearchRecords.Controls("Combobox" & x & "_" & x).Value <> "Parameter" Then
Parameter = SearchRecords.Controls("Combobox" & x & "_" & x).Value
If x = 1 Then Set Filter1 = Sheet3.Range("1:1").Find(Parameter, Sheet3.Range("1:1").Find(Category)) ': Debug.Print Filter1
If x = 2 Then Set Filter2 = Sheet3.Range("1:1").Find(Parameter, Sheet3.Range("1:1").Find(Category)) ': Debug.Print Filter2
If x = 3 Then Set Filter3 = Sheet3.Range("1:1").Find(Parameter, Sheet3.Range("1:1").Find(Category)) ': Debug.Print Filter3
If x = 4 Then Set Filter4 = Sheet3.Range("1:1").Find(Parameter, Sheet3.Range("1:1").Find(Category)) ': Debug.Print Filter4
If x = 5 Then Set Filter5 = Sheet3.Range("1:1").Find(Parameter, Sheet3.Range("1:1").Find(Category)) ': Debug.Print Filter5
'Set TestFilter = Sheet3.Range("1:1").Find("ID", Sheet3.Range("1:1").Find("Feature Type"))
End If
End If
Next x
strSearch = "*" & SearchRecords.TextBox1.Value & "*"
If strSearch = "*Enter Search Text Here*" Or strserch = "**" Then strSearch = "*"
Dim Desc As Range: Set Desc = Sheet3.Range("1:1").Find("Description")
'Set rFound = Sheet3.Range(Sheet3.Cells(2, Desc.Column), Sheet3.Cells(LastRow, Desc.Column)).Find(strSearch)
'Set rFound = Sheet3.Range("H2:H" & LastRow).Find(strSearch, Desc, , , xlNext)
Set rFound = Sheet3.Range("H:H").Find(strSearch)
If rFound Is Nothing Then Exit Sub
If Not rFound Is Nothing Then
sAddr = rFound.Address
Do
Valid = True
If Not IsEmpty(Filter1) Then Set TestFilter = Sheet3.Cells(rFound.Row, Filter1.Column): If TestFilter = False Or TestFilter = 0 Then Valid = False
If Not IsEmpty(Filter2) Then Set TestFilter = Sheet3.Cells(rFound.Row, Filter2.Column): If TestFilter = False Or TestFilter = 0 Then Valid = False
If Not IsEmpty(Filter3) Then Set TestFilter = Sheet3.Cells(rFound.Row, Filter3.Column): If TestFilter = False Or TestFilter = 0 Then Valid = False
If Not IsEmpty(Filter4) Then Set TestFilter = Sheet3.Cells(rFound.Row, Filter4.Column): If TestFilter = False Or TestFilter = 0 Then Valid = False
If Not IsEmpty(Filter5) Then Set TestFilter = Sheet3.Cells(rFound.Row, Filter5.Column): If TestFilter = False Or TestFilter = 0 Then Valid = False
If Valid = True Then
With SearchRecords.ListBox1
.AddItem Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Cust ID").Column).Value 'CustID
' .List(.ListCount - 1, 1) = Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Quote ID").Column).Value 'Quote ID
' .List(.ListCount - 1, 2) = Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Gage #").Column).Value 'Gage #
' .List(.ListCount - 1, 3) = Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Feat #").Column).Value 'Feat #
' .List(.ListCount - 1, 4) = Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Created").Column).Value 'Created
' .List(.ListCount - 1, 5) = Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Modified").Column).Value 'Modified
' .List(.ListCount - 1, 6) = Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Total Cost").Column).Text 'Total Cost
' .List(.ListCount - 1, 7) = Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Description").Column).Value 'Description
End With
End If
Set rFound = Sheet3.Range("H:H").FindNext(rFound)
Loop While rFound.Address <> sAddr
End If
SearchRecords.ListBox1.ColumnWidths = "40;50;40;40;100;100;60;400"
SearchRecords.Label2.Caption = SearchRecords.ListBox1.ListCount & " Matching Records Found"
End Sub