Hi, I need to be able to check multiple worksheets to see if a reference
number already exists. I have tried various functins such as count if etc
and the consilidate function but to no avail. Is there a way that I can
create a macro that checks to see if the number already exists on another
worksheet? This will be in a shared workbook?

Thanks in advance for any help.

VBA code and shared workbooks don't get along. What you are asking for would
be tough enough without the shared restriction. I am not saying that you will
not be able to make it work with a macro but it may be a real up hill battle.
It may also not be possible.

Not tested but something like this might help... No guarantees with a shared
workbook.

dim wks as worksheet
dim rngFound as range

for each wks in worksheets
set rngfound = wks.cells.find(what:="Tada", _
LookAt:=xlwhole, _
LookIn:=xlformulas, _
matchCase:=true)
if not rngfound is nothing then msgbox rngfound.parent.name &
rngfound.address
next wks
--
HTH...

Jim Thomlinson


"RAYCV" wrote:

Hi, I need to be able to check multiple worksheets to see if a reference
number already exists. I have tried various functins such as count if etc
and the consilidate function but to no avail. Is there a way that I can
create a macro that checks to see if the number already exists on another
worksheet? This will be in a shared workbook?

Thanks in advance for any help.

Jim,

Thanks for your response.

Would it then be possible to record a macro that does a Find in the workbook
to see if a number already exists using just a dialogue box, and amessage box
if it does appear rather than using the defualt Ctrl+F and changing the
search range to workbook?

"Jim Thomlinson" wrote:

VBA code and shared workbooks don't get along. What you are asking for would
be tough enough without the shared restriction. I am not saying that you will
not be able to make it work with a macro but it may be a real up hill battle.
It may also not be possible.

Not tested but something like this might help... No guarantees with a shared
workbook.

dim wks as worksheet
dim rngFound as range

for each wks in worksheets
set rngfound = wks.cells.find(what:="Tada", _
LookAt:=xlwhole, _
LookIn:=xlformulas, _
matchCase:=true)
if not rngfound is nothing then msgbox rngfound.parent.name &
rngfound.address
next wks
--
HTH...

Jim Thomlinson


"RAYCV" wrote:

Hi, I need to be able to check multiple worksheets to see if a reference
number already exists. I have tried various functins such as count if etc
and the consilidate function but to no avail. Is there a way that I can
create a macro that checks to see if the number already exists on another
worksheet? This will be in a shared workbook?

Thanks in advance for any help.