numbers in an Array are the Same?

[Frankbelly]

I am adding numbers to an Array via the users selection (user selects a
number and it adds it to an array). What I need is a way to check if there
are two or more numbers that are exactly the same inside the Array. I need
to find out which numbers are duplicates, triplicates, etc.

Any help is greatly appreciated. Thanks.

 

[Jezebel]

One approach is to use a collection rather than an array. Create a key from
the number, something like

on error resume next
MyCollection.Add Item:=MyNum, Key:= "_" & format(MyNum, "0000")
if err.Number <> 0 then
... MyNum has been added already

 

[Frankbelly]

Hi Jezebel,

Thank you so much for your resolution and I believe this is definitely what
I need to use only my skills are extremely low and I have never used "Add
Item" in VBA. I tried looking through the help files, but it still seems
confusing to me.

I'm using this in a document where you have a set of statements with numbers
at the end. I am checking to make sure no two statements have the same
number at the end. Example of statements in the document:

--------------------------------------------------
INTERROGATORY RESPONSE NO. 1

A paragraph of text. The quick brown fox jumps over the lazy dog. The quick
brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy
dog.

INTERROGATORY RESPONSE NO. 2

A paragraph of text. The quick brown fox jumps over the lazy dog. The quick
brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy
dog.

INTERROGATORY RESPONSE NO. 3

A paragraph of text. The quick brown fox jumps over the lazy dog. The quick
brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy
dog.

 

[Jezebel]

Read help on Collections. The instruction is 'Add' -- Item and Key are the
arguments. Apart from the VBA help itself, Google for any of dozens of VB or
VBA tutorial sites (collections are pretty much the same in both). In brief,
a collection is an arbitrary list of Key-Item pairs. The Keys must be
unique, and it makes life easier if they are strings. The items are usually
objects, but can be anything, including literal values.

If your exercise is just a one-off to check for duplications (as opposed to
something you will need to do repeatedly), make a copy of your document,
select all, and sort. Then all your INTERROGATORY RESPONSE lines will come
together, and you can check visually for duplications (or if you really have
squillions, check the MVPS site for how to use Find to look for duplicated
paragraphs).

 

[Frankbelly]

This is great! Thanks, again, for pointing me in the right direction.