cell searching
 

Hi,

I am looking for a way to search through a certain cell on multiple
worksheets, for example: B5 on Sheet 3 through Sheet 45, and when the
cell has a number in it (it is either going to be blank or have a
number), a message box pops up displaying the worksheet name. I want
to insert a button or something that the user can press to start the
search, then the user will input the cell to search in and the program
will run, displaying message boxes for every worksheet that has a
number (only a few will). I would be very very grateful for any help
with this!

Thanks,
-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=380524

this macro should do that



Sub MACRO9()
Dim W As Worksheet
Dim VAL As Variant
VAL = InputBox("Enter which cell to search")
For Each W In Worksheets
W.Select
If (IsNumeric(Range(VAL).Value) And Range(VAL).Value < "")
Then
MsgBox "found number in " & W.Name
End If
Next
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380524

Thanks so much for that macro. Is there a way to have it search all but
one worksheet? The first worksheet in the database is a summary with
differently arranged cells, which messes up the search. If there was a
way to have the macro search all the worksheets but that one then it
would run perfectly.

Thanks,
-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=380524

I am assumming the worksheet you want to skip searching is "Summary",
you can change in the code if the name of worksheet is different than
Summary

Sub MACRO9()
Dim W As Worksheet
Dim VAL,sh_skip As Variant
sh_skip="Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
For Each W In Worksheets
W.Select
if w.name<sh_skip then
If (IsNumeric(Range(VAL).Value) And Range(VAL).Value < "") Then
MsgBox "found number in " & W.Name
End If
end if
Next
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380524

Thank you very very much that works perfectly! I know practically
nothing about Visual Basic, so I'm trying to understand this code...

"If W.Name < sh_skip Then", how does this line make the program skip
the summary worksheet?

"If (IsNumeric(Range(VAL).Value) And Range(VAL).Value < "") Then", for
this line, does the And Range(VAL).Value < "" make the code skip over
the cells with no text in them, which the "" represents? Do these: <
make the code skip what they are referring to?

Sorry to keep bothering you, I just want to try and learn exactly how
this code is working.

Thanks,
-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=380524

For Each W In Worksheets ---- (1)
W.Select ---- (2)
if w.name<sh_skip then ------(3)
If (IsNumeric(Range(VAL).Value) And Range(VAL).Value < "") Then
----(4)
MsgBox "found number in " & W.Name ----(5)
End If-----(6)
end if----(7)
Next----(8)

(1)- looping through all the worksheets
(2)-selects a worksheet
(3)w.name<sh_skip i.e if worksheet name is not equal to
sh_skip(Summary) then only execute the code(next line) otherwise goes
to line (7)
(4)checks whether numeric and as no text(Range(VAL).Value < ""), if
true goes to (5)
(5) alerts with worksheet name
(6) ,(7) end of id statements
(8) end looping through worksheets
(6)


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380524

Thanks anilsolipuram!


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=380524