cell searching

[thephoenix12]

Hi,

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

Thanks,
-Stev

 

[anilsolipuram]

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 Su

 

[thephoenix12]

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

 

[anilsolipuram]

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

 

[thephoenix12]

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

 

[anilsolipuram]

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)

 

[thephoenix12]

Thanks anilsolipuram!