0

So I have been trying to figure out why this isn't working and I am getting really frustrated.

When I click on my "Search" button, it takes the text from a text box, and uses that as the search criteria. I want it to skip whatever row is currently active, so that as long as I keep pressing my "Search" buttton it will move on, and not keep finding the same row. And I had it working for a long time, then I upgraded to Windows 10 and stuff stopped working. This is just the one thing I can't seem to figure out. I made some changes to my original, so this is not the same code that used to work. No matter what I do it keeps showing me the same row even though the one RIGHT BELOW it has identical data. Like in the picture below, if I search for TRACE the third row of data is selected, but when I hit "Search" again, it doesnt move to the next row like it should. I am using Range.Find(What:= , After:=) and setting the After range to the very left cell of the current activated range. Which should start the search on the next row. But that is not happening.

enter image description here

Private Sub Search_Next_Click()
    Dim Fnd As Range
    Dim S_Range as Range
    Dim CurrRow As Integer: CurrRow = ActiveCell.Row
    Dim CurrColumn As Integer: CurrColumn = ActiveCell.Column

'Last row of data
    LastRow = Range("B24").End(xlDown).Row

    AC = ActiveCell.Address
''If the Find button is pressed and the current active cell is outside the range of my data
''this makes sure that the active cell moves to the upper left of that range

    If AC = "" Or CurrRow < 24 Or CurrColumn > 10 Then
        AC = "B24"
        Range(AC).Activate
    End If
    ACr = ActiveCell.Row
    On Error Resume Next
    Set S_range = Range("B24" & ":J" & LastRow)

    Set Fnd = S_range.Find(what:=SearchBox.Text, after:=Range(AC))
    FR = Fnd.Row
    If FR = "" Then
        MsgBox ("No Match Found")
        DoCmd.CancelEvent
        SearchBox.SetFocus
        Exit Sub
    End If
    On Error GoTo 0
    Scell = "B" & FR & ":J" & FR
    ActiveSheet.Range(Scell).Select
    ActiveSheet.Range(Scell).Activate
End Sub
Community
  • 1
  • 1
Tucker Black
  • 21
  • 2

1 Answers1

0

OK as soon as I posted this, I figured out what I did wrong. Although I dont know why it worked in the past, and then stopped. Maybe I changed something without realizing it and screwed myself up. Anyway, I was searching for a value in the D column. Once it was found the cell with the search criteria, the cells in column B through F were selected and activated in that row. But when I hit Search again, it started after the B column of that row. I thought it would skip to the next row, but it was moving to the next column to start the search. So it ran into the same value again, and the cycle repeated.

I just changed the After criteria to

After:=Range("B" & ACr + 1)

Then it started the search on the next row like I wanted. Im leaving this up in case anyone else comes across this mistake. Hopefully I can help someone else realize that they are not the only ones that make stupid mistakes.....

Tucker Black
  • 21
  • 2
  • Good stuff, but it would be worth your while reading https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?s=1|242.6149 – SJR May 01 '18 at 18:15
  • @ SJR Thanks for the info. I have used SELECT and ACTIVE___ since I started learning VBA and it has lead to some headaches at times. Without knowing it I have gradually moved away from it, in favor of more descriptive and robust methods. But the pieces in the code above are reminants of when I was first building the project. I try not to mess with what works. But as the use of the workbook scales up, I know I dont want to go back and make changes to solve problems that didnt need to exist. Thank you again! – Tucker Black May 01 '18 at 18:30
  • 1
    Take your point but chances are that one day it will trip you up. Every day this place is full of problems caused by people using select/activate. – SJR May 01 '18 at 18:33
  • OK sorry I misunderstood. – SJR May 01 '18 at 19:13