I am going bonkers trying to figure out why this is happening,

[figment222]

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...

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

 

[mole999]

Re: 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 thread

first thought is are you on sheet3

sheets("name").range might save some issues if they are reordered / deleted / added to

 

[figment222]

Re: 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 thread

first thought is are you on sheet3

sheets("name").range might save some issues if they are reordered / deleted / added to

Yes, sheet3 is active when I've been debugging, but I was using another version of this macro with no issues until yesterday. The only thing that changed is that I inserted another column in "E", which meant that I had to do rename the rFound column to search from "G" to "H". I also had the section where the listbox was being populated by the other columns called out specifically by column letters, so I decided to be a little smarter and use the range.find function to get the proper column. That way, if I add another column later, I wouldn't have to rewrite the macro, unless I wanted to also include the new column in the listbox.

so, it went from:

.AddItem Sheet3.Cells(rFound.Row, "A").Value 'CustID
.List(.ListCount - 1, 1) = Sheet3.Cells(rFound.Row, "B").Value 'Quote ID

etc

to:

.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

while troubleshooting, I added a watch for rFound.Address and rFound.Value. I put a stop on the line towards the bottom for:
Loop While rFound.Address <> sAddr

I commented out everything in the loop and one by one, removed the comment mark to find out what was causing the problem. Everything seemed to work ok, until I removed the comment mark for ".add item"

It seems to happen at the ".add item" line, (which is why the lines for: ".list(.listcount -1,1)" are still commented out), causes the rFound variable to reset, which then causes the line for: "Set rFound = Sheet3.Range("H:H").FindNext(rFound)" to not function the way it should. After the first correctly found "*" in the description column (Row2), it winds up skipping around on the list searching for "*" in the description column and only seems to find values that say "Description", preventing the ability for "sAddr" to ever again be the same as the "rFound.Address", so the loop just continues on, adding items until i manually CTRL+Break

 

[figment222]

Re: 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 thread

I might also add that my original post was NOT intended to have a blank subject line. I was in a hurry to post it and had to run as soon as I submitted. it was beyond the 10-minute editing window by the time I came back and noticed the forum faux pas of such a non-descript subject line.

 

[figment222]

Re: 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 thread

Solved: (Kinda)

After more troubleshooting, I resorted to something that didn't make a lick of sense to me, but at least had potential.
since it was the .add item line that was 'somehow' messing up the ability to set rFound = Sheet3.Range("H:H").FindNext(rFound), then I had a closer look at what I was adding

.AddItem Sheet3.Cells(rFound.Row, Sheet3.Range("1:1").Find("Cust ID").Column).Value 'CustID

the culprit was setting the column via range.find. I declared and set this range ahead and then I could simplify this line a bit, as follows:

Dim CustID As Range: Set CustID = Sheet3.Range("1:1").Find("Cust ID")
.AddItem Sheet3.Cells(rFound.Row, CustID.Column).Value 'CustID

I don't understand it, but for whatever reason, separating those two elements prevented the .FindNext(rFound) from resetting the rFound range object and looping endlessly. If anybody has an explanation for that, i'd love to hear it.