Archive of Mr Excel Message Board

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

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

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

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

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

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

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

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

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

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

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

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

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