Hello -

There are several sheets in a workbook. Apart from the first sheet named
"SHEET1" every sheet contains lots of strings (text, numbers, etc).

I need a macro that will loop through each sheet except "SHEET1",
searching for matches of the word or phrase in Range("A1") of "SHEET1".
Conditions:

1. The search match should not be case sensitive.

2. It is possible a match could be found in several cells on one sheet
AND/OR found in several other sheets.

3. For each match found on a sheet, the cell containing the match should
be selected. Then an option box should pop up and ask whether I want to
continue searching the rest of the sheets or return to "SHEET1"..(i.e
"Yes Continue Search", "No Return to SHEET1"). If no matches are found
in the workbook, popup box should just say "No matches found"

Any help would be appreciated!
Thanks
Jay




*** Sent via Developersdex http://www.developersdex.com ***

Please refer this link from Chip Pearson..
http://www.cpearson.com/excel/RangeFind.htm

If this post helps click Yes
---------------
Jacob Skaria


"jay dean" wrote:

Hello -

There are several sheets in a workbook. Apart from the first sheet named
"SHEET1" every sheet contains lots of strings (text, numbers, etc).

I need a macro that will loop through each sheet except "SHEET1",
searching for matches of the word or phrase in Range("A1") of "SHEET1".
Conditions:

1. The search match should not be case sensitive.

2. It is possible a match could be found in several cells on one sheet
AND/OR found in several other sheets.

3. For each match found on a sheet, the cell containing the match should
be selected. Then an option box should pop up and ask whether I want to
continue searching the rest of the sheets or return to "SHEET1"..(i.e
"Yes Continue Search", "No Return to SHEET1"). If no matches are found
in the workbook, popup box should just say "No matches found"

Any help would be appreciated!
Thanks
Jay




*** Sent via Developersdex http://www.developersdex.com ***

You could try something like this:

Sub Find_Value
MySheet = 1

Do Until MySheet = Sheets.Count + 1
Sheets(MySheet).Select
If ActiveSheet.Name = "Sheet1" Then
Goto Next_Sheet
End If

Next_Find:
Cells.Find What:="Sheet1", MatchCase:=False
If Activecell.Address = FirstMatch Then
Goto Next_Sheet
End If
If FirstMatch = "" Then
FirstMatch = Activecell.Address
End If
ValueFound = MsgBox("Have found value at " & Activecell.Address & ".
Continue search?",vbYesNo,"Search Sheets")
If ValueFound = vbYes Then
MsgBox("Search continuing")
Else
Goto End_Me
End If
Goto Next_Find

Next_Sheet:
FirstMatch = ""
MySheet = MySheet + 1
Loop

End_Me:

End Sub

You would probably also need to put in an error handler if the value wasn't
found at all on a sheet. If the value is going to be in a particular,
consistent column, you could just loop through the cells looking for the
value using the StrCompare statement.


"Jacob Skaria" wrote:

Please refer this link from Chip Pearson..
http://www.cpearson.com/excel/RangeFind.htm

If this post helps click Yes
---------------
Jacob Skaria


"jay dean" wrote:

Hello -

There are several sheets in a workbook. Apart from the first sheet named
"SHEET1" every sheet contains lots of strings (text, numbers, etc).

I need a macro that will loop through each sheet except "SHEET1",
searching for matches of the word or phrase in Range("A1") of "SHEET1".
Conditions:

1. The search match should not be case sensitive.

2. It is possible a match could be found in several cells on one sheet
AND/OR found in several other sheets.

3. For each match found on a sheet, the cell containing the match should
be selected. Then an option box should pop up and ask whether I want to
continue searching the rest of the sheets or return to "SHEET1"..(i.e
"Yes Continue Search", "No Return to SHEET1"). If no matches are found
in the workbook, popup box should just say "No matches found"

Any help would be appreciated!
Thanks
Jay




*** Sent via Developersdex http://www.developersdex.com ***