Finding a value and going to that cell?

[Simon Lloyd]

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

[Jim Thomlinson]

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
>
>

[Simon Lloyd]

Hi Jim Thanks for the reply, i had to add an End If before Loop Until but other than that it worked, i added an input box at the begining to decide which string to search for, your code brought up the message box but without any content (i would like to show the contents of the cell it found here) and when i clicked yes it did not transport me to the cell that contained the one i said yes to......is it possible you could have a look at it and see if the above additions are possible?

Kind regards,
Simon

[Jim Thomlinson]

You could get away without the end if but the code wrapped when I posted it.
Here is a slightly modified version of the procedure.

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
application.screenupdating = true

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... " & rng.Text, _
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 Jim Thanks for the reply, i had to add an End If before Loop Until
> but other than that it worked, i added an input box at the begining to
> decide which string to search for, your code brought up the message box
> but without any content (i would like to show the contents of the cell
> it found here) and when i clicked yes it did not transport me to the
> cell that contained the one i said yes to......is it possible you could
> have a look at it and see if the above additions are possible?
>
> Kind 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
>
>

[Simon Lloyd]

Jim, thanks for the revised code i am still using the input box to find the text and it works a treat, thanks for your efforts!

Regards,
Simon