Compare two arrays in VBA to find dupes
 

I have two simple array variables in some VBA code. I'm wondering
what is the simplest way to compare the contents (typically less than
50 items in each array) to find dupes. Specifically, I would do this:

If arFirstList (i) is in arSecondList then
Do this stuff....
Else
Do this other stuff
End if

Is there a function that would handle this or do I need to create a
For Next loop that completely cycles through the arSecondList each
time I go to a new member in arFirstList?

Thanks in advance.

- John

Compare two arrays in VBA to find dupes
 

If we make a concatenated version of the second list:

t = aSecondList(0)
For i = 1 To UBound(aSecondList)
t = t & "!" & aSecondList(i)
Next

Then all we have to do is check each value in the first list against t using
InStr()
--
Gary's Student
gsnu200704


"John Michl" wrote:

I have two simple array variables in some VBA code. I'm wondering
what is the simplest way to compare the contents (typically less than
50 items in each array) to find dupes. Specifically, I would do this:

If arFirstList (i) is in arSecondList then
Do this stuff....
Else
Do this other stuff
End if

Is there a function that would handle this or do I need to create a
For Next loop that completely cycles through the arSecondList each
time I go to a new member in arFirstList?

Thanks in advance.

- John

Compare two arrays in VBA to find dupes
 

Dim i as Long, res as Variant
for i = lbound(arFirstList) to Ubound(arFirstList)
res = application.Match(arFirstList(i),arSecondList,0)
if not iserror(res) then
do this stuff
else
do this other stuff
end if
Next

--
Regards,
Tom Ogilvy



"John Michl" wrote:

I have two simple array variables in some VBA code. I'm wondering
what is the simplest way to compare the contents (typically less than
50 items in each array) to find dupes. Specifically, I would do this:

If arFirstList (i) is in arSecondList then
Do this stuff....
Else
Do this other stuff
End if

Is there a function that would handle this or do I need to create a
For Next loop that completely cycles through the arSecondList each
time I go to a new member in arFirstList?

Thanks in advance.

- John