finding a value

T

tom

Hi,

How can I find a certain value, say x, in all the sheets
in a workbook or excel file? After finding it, I must
output all the sheet names that has x and its cell
location on a msgbox.

Thanks.
 
M

Mark

try this
sub findX()
dim i as integer
dim cell

set X=activecell.value
for i=1 to sheets.count
sheets(i).select
range("A1").select
set a=selection
range(a,a.specialcells(xlcelltypelastcell)).select
for each cell in selection
if cell.value=X.value then
msgbox "X.value is in sheet " & sheet.name
exit for
end if
next
next
end sub

regards
Mark
PS
see my Excel site
http://www.geocities.com/excelmarksway
 
Top