G
GTS
Hello people,
i'm not a programmer and i know only a few things about VB. My problem
is this:
i wanted to create a macro where i would be able to perform the search
action in all worksheets of an excel workbook. So, i created a "Find
All" menu item and when it is clicked a search-like form is opened. The
search is performed like this:
Private Sub FindCB_Click() 'This is the Find Next button in my form
Dim sh As Worksheet
Dim rng As Range, firstAddress As String
For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*",
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
If Not rng Is Nothing Then
Application.Goto rng, True
End If
Set rng = sh.Cells.FindNext(rng)
Loop Until (rng.Address = firstAddress)
End If
Next
So, when the code is executed it goes directly into the last string it
finds, skipping all the intermediate strings. How can i make it stop in
every string it finds? And then by clicking the button to go to the
next one? Like the classic Find option in excel?
Thank you very much in advance!
i'm not a programmer and i know only a few things about VB. My problem
is this:
i wanted to create a macro where i would be able to perform the search
action in all worksheets of an excel workbook. So, i created a "Find
All" menu item and when it is clicked a search-like form is opened. The
search is performed like this:
Private Sub FindCB_Click() 'This is the Find Next button in my form
Dim sh As Worksheet
Dim rng As Range, firstAddress As String
For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*",
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
If Not rng Is Nothing Then
Application.Goto rng, True
End If
Set rng = sh.Cells.FindNext(rng)
Loop Until (rng.Address = firstAddress)
End If
Next
So, when the code is executed it goes directly into the last string it
finds, skipping all the intermediate strings. How can i make it stop in
every string it finds? And then by clicking the button to go to the
next one? Like the classic Find option in excel?
Thank you very much in advance!