VBA Loop through Find Results

[hawksbowl2014]

Hello,
I have a Userform that has a Search box so a user can search for key words. While it does work and will select all relevant cells, one cannot cycle through the results like the built in FindNext funtion. I would like the 'NextResultButton' to progress the ActiveCell through the search results selected cells. Here is the current Search code (attempted Loop posted below) :

---

Private Sub SearchButton_Click()
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
Dim ws As Worksheet

  fnd = (TextBox1.Value)
  
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

  If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
  Else
    GoTo NothingFound
  End If

Set rng = FoundCell

  Do Until FoundCell Is Nothing
      Set FoundCell = myRange.FindNext(after:=FoundCell)
      Set rng = Union(rng, FoundCell)
   
      If FoundCell.Address = FirstFound Then Exit Do
      
  Loop
  
rng.Select

Exit Sub

NothingFound:
  MsgBox "No Matches were Found"

End Sub

---

And here is what I was trying to use to be able to cycle through:

---

Private Sub NextResultButton_Click()
With Worksheets(1).Range("a4:k500")
    Set c = .Find("textbox1.value")
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = ActiveCell.Select
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

End Sub

---

~ Thank you

 

[shg]

Maybe ...

Private Sub NextResultButton_Click()
  On Error GoTo Oops
  Range("A4:K500").Find(What:=textbox1.Value, After:=ActiveCell).Select
  Exit Function
  
Oops:
  Beep
End Sub

 

[hawksbowl2014]

It does work! Thank you - I did modify to remove the beep:

Private Sub NextResultButton_Click()
  On Error GoTo 0
  Range("A4:K500").Find(What:=TextBox1.Value, After:=ActiveCell).Select

End Sub

But am wondering if you, or anyone for that matter, would be able to help me implement the below code - I can set it as the Target for everything but am hoping to just make it part of the FindNext function but when I tried to merge @shg code with this I got nothing but errors:

<code style="margin: 0px; padding: 0px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit; background-color: rgb(238, 238, 238);">[COLOR=#00008B]Private[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Sub[/COLOR][COLOR=#000000] Worksheet_SelectionChange[/COLOR][COLOR=#000000]([/COLOR][COLOR=#00008B]ByVal[/COLOR][COLOR=#000000] Target [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] Range[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Static[/COLOR][COLOR=#000000] rng [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] Range

    [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Not[/COLOR][COLOR=#000000] rng [/COLOR][COLOR=#00008B]Is[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]Nothing[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000] rng[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]FormatConditions[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Delete
    [/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#000000] rng [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] Target
    [/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] rng[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]FormatConditions[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Add[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Type[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]xlExpression[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] Formula1[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#800000]"=TRUE"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Interior[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]PatternColorIndex [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] xlAutomatic
        [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Interior[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Color [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]49407[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Sub[/COLOR]</code>

 

[shg]

What are you trying to do?

 

[hawksbowl2014]

Currently the selected cell has the standard border around it - was trying to add a temporary fill color to accentuate the selected cell as I cycle through. The above code allows selecting a cell without removing its conditional formatting was the nice bonus.

 

[shg]

Doing that kills Undo, which, IMO, is a very high price to pay for highlighting the selection.

 

[hawksbowl2014]

Thank you - I hadn't realized it negated the Undo. I found this Code with minimal Conditional Formatting enabled the highlighting of the active cell and kept the Undo, Copy, Paste functions fully intact.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)  

Application.ScreenUpdating = True
    
End Sub

And then select your range and enter this as a Conditional Format under 'Use a Formula..' and format as desired:

=CELL("address")=ADDRESS(ROW(),COLUMN())

 

[shg]

That's OK, I guess, but conditionally formatting every used cell on a large worksheet is going to be expensive, performance-wise.