MrExcel Publishing
Your One Stop for Excel Tips & Solutions

need a macro to search all of the sheets in a workbook


Posted by Kevin on September 13, 2001 8:46 AM

I need a macro or something that will allow the user to type a word, name, or number, or something into a cell and then click a "find" button, that will then search all of the sheets in the workbook (there are around 12 sheets right now) and find the data that the user typed in the cell. Anyone have an idea how to do this easily?

Thanks in advance,
Kevin


Posted by Barrie Davidson on September 13, 2001 9:24 AM

Kevin, try this code

Sub Find_Data()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
For counter = 1 To sheetCount
Sheets(counter).Activate
If IsError(Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate) = False Then Exit For
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub


If you have any problems let me know.

Regards,
BarrieBarrie Davidson

Posted by Kevin on September 13, 2001 9:37 AM

not sure I understand

Barrie,
I tried inserting this macro and running it. It only searches and finds data in my first sheet (Sheet1) but does not search all of the sheets. Even if I activate one of the other sheets and run the macro from that sheet, I get a message that says, "Value not found" and then I am returned to the first sheet in the workbook. Any way to modify this code to make it search all the sheets in the workbook for the search text?

Thanks for your help,
Kevin

Posted by Barrie Davidson on September 13, 2001 9:47 AM

Re: not sure I understand

Kevin, are you searching for a value or text? This macro will search for string values only. Did you want it to search for values instead?

BarrieBarrie Davidson

Posted by Kevin on September 13, 2001 10:07 AM

Re: not sure I understand

Barrie,

Actually I would like to search for both values (serial numbers for example) and/or text (a customer name). However, I was having the problem even when I was trying to search for a text string.

Thanks,
Kevin

Posted by Barrie Davidson on September 13, 2001 10:39 AM

Re: not sure I understand

Kevin, I changed the code slightly to accomodate searching for text or numbers. Here it is

Sub Find_Data()
' Written by Barrie Davidson
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
For counter = 1 To sheetCount
Sheets(counter).Activate
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
If IsError(Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate) = False Then Exit For
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub

Give it another go and let me know if you have any problems (via this board would be best).

Regards,
BarrieBarrie Davidson

Posted by Barrie Davidson on September 13, 2001 10:39 AM

Also note that the code is case sensitive.(nt)

Posted by Kevin on September 13, 2001 11:35 AM

the code does not seem to work

Barrie,

The code does not work for me for neither text nor numbers. The code only works if the first sheet in my workbook is the active sheet. If I try to search for data in one of the sheets, it reverts back to the first sheet. For example, if I have 10001600474 in both sheet1 and sheet2, and use this search macro, it will locate the number in sheet1, but if I delete the number in sheet1, and then rerun the macro, it will not find the number in sheet2, but will return the error "value not found"

Thanks,
Kevin

Posted by Barrie Davidson on September 13, 2001 11:47 AM

Re: the code does not seem to work

Kevin, I think I found the problem. Try this instead.

Sub Find_Data()
' Written by Barrie Davidson
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit For
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub

Regards,
BarrieBarrie Davidson

Posted by Barrie Davidson on September 13, 2001 12:48 PM

Ignore the above message and use this code.


I guess I'm more tired than I thought :)

Sub Find_Data()
' Written by Barrie Davidson
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub


Barrie

Barrie Davidson

Posted by Kevin on September 13, 2001 1:23 PM

Re: Ignore the above message and use this code.

Barrie,

Now when I insert this code into the spreadsheet and run it, the macro scrolls through all of the sheets, but at the end still gives me the error message "Value not found", even though the value is located somewhere in the spreadsheet.

Thanks,
Kevin

Posted by Barrie Davidson on September 13, 2001 1:28 PM

Re: Ignore the above message and use this code.

Did you use the first code or the second code in the message? You need to use the second code. Here it is.

Sub Find_Data()
' Written by Barrie Davidson
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub


Regards,
BarrieBarrie Davidson

Posted by Kevin on September 13, 2001 2:13 PM

yes, I am using the second code, but...

I am still having the problems that I mentioned in my previous post. Specifically, when I run the macro, I get the message box "Please enter the value to search for". I enter a name that I know is on the third sheet in my workbook, and press the OK button. The macro then starts with my first sheet, and scrolls through the sheets (as if I was pressing the Crl+PgDn buttons), and then gives me the message "Value not found".

Thanks for sticking w/ me on this,
Kevin

Posted by Barrie Davidson on September 13, 2001 2:27 PM

A mystery, hmmmm

Kevin, you mention you are searching for a name. Are you typing in the name exactly as how it is entered in sheet3 (case sensitive)? I would suggest testing it by going to the cell in Sheet3 and pressing F2 (edit mode). Then select the entire data in that cell and press CTRL+C (copying the data to the clipboard), then press ESC to get out of edit mode. Then run your macro. When prompted for the search value, press CTRL+V (pasting the data). I suspect you either have a space you are missing or the case is different (capital C instead of small c).

Try that and let me know how it works for you.

Regards,
Barrie (Sherlock Holmes wannabe)Barrie Davidson

Posted by Kevin on September 13, 2001 2:49 PM

mystery solved

Barrie,
You were right - when I typed the data exactly as it appears in the sheet (or pasted it) your macro worked. Thanks!!! Do you have another macro or know how to modify this particular macro to do the same search for values? If not, would this macro work for finding serial numbers if I formatted all of the cells that contain numbers as text? Thanks for your help with this, it was for work - much appreciated.

Kevin

Posted by Barrie Davidson on September 13, 2001 2:57 PM

The macro will search for text or values (nt)