PDA

View Full Version : Search / Find - across sheets - find multiple instances



bdsii
01-29-2010, 07:30 AM
I have the code below to search across multiple sheets of an active workbook and I have modified it to search inside a cell instead of searching for a complete cell value which helps find partial matches.

I need this find function to move to the next instance of a match until all sheets have been searched.

I would prefer this to function as the Find function inside Excel where you can click Next to take you to the next instance but this code stops when it finds the first match. I would like the option to click Next to move to the next one or click Exit or something to stop it at the current selection that it has found, once again similar to the Find function in Excel.

I believe a .FindNext function would work but figure out how to incorporate it into the code.


Any ideas ?


thanks !





Sub FindData()
Dim MyData As String
Dim wks As Worksheet
Dim rngFoundData As Range
Dim firstaddress As String

' get users data
MyData = InputBox("Please enter the value to search for.")
If MyData = "" Then Exit Sub
' search all sheets in workbook
For Each wks In Worksheets
' find data in current worksheet
'' LookAt:=xlWhole
Set rngFoundData = wks.Cells.Find(What:=MyData, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
' select found data and exit sub
If Not rngFoundData Is Nothing Then
wks.Activate
rngFoundData.Select
Exit Sub
End If
Next wks

' tell user data wasn't found
If rngFoundData Is Nothing Then
MsgBox MyData & " was not found in " & ActiveWorkbook.Name, vbInformation
End If
End Sub

MaximS
01-29-2010, 10:05 AM
What about making UserForm with hidden field holding last found instance address.
Then after clicking next will start from that place.

bdsii
01-31-2010, 11:55 AM
Thanks but I was hoping to avoid the userform. Any ideas ?

GTO
01-31-2010, 12:22 PM
...I would like the option to click Next to move to the next one or click Exit or something to stop it at the current selection that it has found, once again similar to the Find function in Excel...


What about making UserForm with hidden field holding last found instance address.
Then after clicking next will start from that place.


Thanks but I was hoping to avoid the userform. Any ideas ?

Errr???:bug:

Hi bdsii,

You mention clicking a Next or Exit - presumably buttons - and we're going to search all sheets, so the button cannot just be on a sheet. If not a userform, what were you thinking of?

Mark

Jan Karel Pieterse
02-01-2010, 04:09 AM
Why roll your own search function, when the work has been done already:
www.jkp-ads.com/officemarketplaceff-en.asp (http://www.jkp-ads.com/officemarketplaceff-en.asp)