"Evaluate" looks like exactly the way I want to go! Thank you, Tom!

Ed

"Tom Ogilvy" wrote in message
...
array functions are only handled by Excel. You can use the evaluate
function to have excel do this for you.

Dim res as Variant
res = Application.Evaluate("Max(Countif(A1:A20,A1:A20))" )
if not iserror(res) then
if res 1 then
msgbox "duplicates"
else
msgbox "No duplicates"
End if
else
msgbox "errors in data or forumla"
end if


If you need the range to be a variable you can use something like

s = Range("A1:A20").Address(0,0)
res = Application.Evaluate("Max(Countif(" & s & "," & s & "))")



--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
Tom:

Thank you for the link. Chip's first formula:
=IF(MAX(COUNTIF(Range1,Range1))1,"Duplicates","No Duplicates")
seems to do exactly what I want.

However, I'm spotty both on using arrays and using worksheet formulas in
VBA. As this is an array formula, how should I implement this? Or what
details have I left out that would allow that kind of advice?

Ed

"Tom Ogilvy" wrote in message
...
You are light on details. But basically, you would need to check
after
or
as part of a change, that the new value is not a duplicate. Chip
Pearson's
provides several generic techniques which might offer some ideas you
can
use:
http://www.cpearson.com/excel/duplicat.htm

another page of interest might be on utilizing events
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
I've got almost 30 ranges in my worksheet. No value should be
duplicated
in
any of these ranges. Is there a convenient way, either with a
worksheet
function or a VBA method, to monitor this for each range? Or do I
need
to
loop through each cell of each range on a regular basis to see if
I've
got
duplicates?

Ed