comparing elements from 2 arrays using VBA code

  • Thread starter Thread starter lopsided
  • Start date Start date
L

lopsided

Hi,
I am having a bit of trouble with arrays. I have created two list
arrays which are of different sizes and have some of the same numbers
in them. I want to go through one list and compare every element within
it with every element on the second list. I need to find out
(basically) which numbers are in the first array but not the second.

The outline of the code i am using is as follows:

i = 1
j = 1
For i = LBound(array1) To UBound(array1)
Do While array1(i) <> array2(j)
For j = LBound(array2) To UBound(array2)
If j = UBound(array2) Then
~~~Return Some Values~~~
GoTo GetOut
Else: End If
Next j
Loop
GetOut:
Next i

The problem lies in that although there are no bugs in my code the
macro is returning all the data (as if the lists had nothing in
common).

Hope someone can help me!!

Thanks a lot
Tom
 
Thanks for your help it seems like your method is more powerful... but i
think the problem i may have been having is that for some reason on
array element would be displayed as "24" and another as 24 and the
coding then said they are different.

But ive sorted that now
Thanks for your help!
 
24 is different from "24"

In some cases, excel will coerce the comparison to the same data type and
the results would be true. I assume there are other cases where it won't.
 
Just another idea. I think you are working with only numbers.
... which numbers are in the first array but not the second.
Click to expand...

This returns the Complement, those that are in A, and not in B. (4,11,12 &
13)
I'm not sure how fast this would be with large arrays though.

Sub Complement()
Dim i As Long
Dim t As Long
Dim p As Long

Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant

v1 = Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
v2 = Array(5, 6, 7, 8, 9, 10)
ReDim v3(0 To UBound(v1))

p = 0
On Error Resume Next
With WorksheetFunction
For i = LBound(v1) To UBound(v1)
t = .Match(v1(i), v2, 0)
If t = 0 Then
v3(p) = v1(i)
p = p + 1
Else
t = 0
End If
Next i
End With
ReDim Preserve v3(1 To p)
End Sub
 
Back
Top