Checking for duplicates
 

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.

Checking for duplicates
 

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.

Checking for duplicates
 

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.