Here is a macro that finds the word Tada in sheets 1 and 2.
Sub FindStuff()
Dim colWks As Collection
Dim wks As Worksheet
Dim rng As Range
Dim strFirst As String
Set colWks = New Collection
colWks.Add Sheets("Sheet1"), Sheets("Sheet1").Name
colWks.Add Sheets("Sheet2"), Sheets("Sheet2").Name
For Each wks In colWks
Set rng = wks.Cells.Find(What:="Tada", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rng Is Nothing Then
strFirst = rng.Address
Do
wks.Select
rng.Select
If MsgBox("How about this one...", vbYesNo, "Found One") =
vbYes Then _
Exit Sub
Set rng = wks.Cells.FindNext(rng)
Loop Until rng.Address = strFirst
End If
Next wks
MsgBox "Sorry, that was all of them", vbInformation, "All Done"
End Sub
--
HTH...
Jim Thomlinson
"Simon Lloyd" wrote:
Hi all, is it possible to search for a value or name through an array of
sheets(for now lets say sheets 1 - 3) and when that value or name is
found go to that cell no matter which sheet, if more than 1 sheet is
found with the value give the option to move on to subsequent sheets or
stay with the first sheet it stopped at?
I'm probably asking a bit much on this one!
Hope you can help!
Regards,
Simon
--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=552555