Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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


Check out our Excel VBA Resources

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

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


not sure I understand

Posted by Kevin on September 13, 2001 9:37 AM
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


Re: not sure I understand

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

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


Re: not sure I understand

Posted by Kevin on September 13, 2001 10:07 AM
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


Re: not sure I understand

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

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


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

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


the code does not seem to work

Posted by Kevin on September 13, 2001 11:35 AM
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


Re: the code does not seem to work

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

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


Ignore the above message and use this code.

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


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


Re: Ignore the above message and use this code.

Posted by Kevin on September 13, 2001 1:23 PM
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


Re: Ignore the above message and use this code.

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

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


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

Posted by Kevin on September 13, 2001 2:13 PM
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


A mystery, hmmmm

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

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


mystery solved

Posted by Kevin on September 13, 2001 2:49 PM
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


The macro will search for text or values (nt)

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.