Sleeper: Comparing arrays

[mdmackillop]

I'm looking for a list of items from Data1, not contained in Data2. Any other methodologies simpler/better than this?

Dim Test1, Test2, Result(20)
    Test1 = Range("Data1").Value
    Test2 = Range("Data2").Value
    For Each x In Test1
        pos = 0
        On Error Resume Next
        pos = Application.WorksheetFunction.Match(x, Test2, 0)
        If pos = 0 Then
            i = i + 1
            Result(i) = x
        End If
    Next

[malik641]

Lookin pretty good to me. Anything specific you want to do to it?

[Bob Phillips]

I'm looking for a list of items from Data1, not contained in Data2. Any other methodologies simpler/better than this?

Dim Test1, Test2, Result(20)
    Test1 = Range("Data1").Value
    Test2 = Range("Data2").Value
    For Each x In Test1
        pos = 0
        On Error Resume Next
        pos = Application.WorksheetFunction.Match(x, Test2, 0)
        If pos = 0 Then
            i = i + 1
            Result(i) = x
        End If
    Next

Couple of things. By using Application.MATCH, and by virtue of the fact that you don't re-use the variable pos, you can dispense with it altogether.

You also don't populate item 0 of the array, so best not declare it.

Finally, you leave Resume Next on, which you shouldn't, but by just using Application.MATCH you don't need it anyway.

Dim Test1, Test2, Result(1 To 20)
Dim x
Dim i As Long
    Test1 = Range("Data1").Value
    Test2 = Range("Data2").Value
    For Each x In Test1
        If IsError(Application.Match(x, Test2, 0)) Then
            i = i + 1
            Result(i) = x
        End If
    Next

[MWE]

...
You also don't populate item 0 of the array, so best not declare it.

why?

[Bob Phillips]

why?

TRy this and see if you can work it out yourself

Dim Test1, Test2, Result(20)
Dim x
Dim i As Long
Test1 = Range("Data1").Value
Test2 = Range("Data2").Value
For Each x In Test1
    If IsError(Application.Match(x, Test2, 0)) Then
        i = i + 1
        Result(i) = x
    End If
Next
For i = LBound(Result) To UBound(Result)
    If Result(i) = "" Then
        If i = LBound(Result) Then
            MsgBox "array is empty"
            Exit For
        End If
    End If
Next i

[TonyJollans]

It is good practice to explicitly declare everything. Nothing shows this better than jumping to the conclusion that an unspecified lbound is 0.

[mdmackillop]

Thanks all,
Good suggestions Bob, I'll make the changes in my code.
Regards
Malcolm