Finding a value and going to that cell?
 

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

Finding a value and going to that cell?
 

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

Finding a value and going to that cell?
 

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

Kind regards,
Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=55255

Finding a value and going to that cell?
 

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

Finding a value and going to that cell?
 

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


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