PDA

View Full Version : Sleeper: Comparing arrays



mdmackillop
08-16-2005, 12:03 PM
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
08-16-2005, 07:16 PM
Lookin pretty good to me. Anything specific you want to do to it?

Bob Phillips
08-17-2005, 02:52 AM
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
08-17-2005, 05:06 AM
...
You also don't populate item 0 of the array, so best not declare it.


why?

Bob Phillips
08-17-2005, 05:37 AM
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
08-17-2005, 06:49 AM
It is good practice to explicitly declare everything. Nothing shows this better than jumping to the conclusion that an unspecified lbound is 0.

mdmackillop
08-17-2005, 03:28 PM
Thanks all,
Good suggestions Bob, I'll make the changes in my code.
Regards
Malcolm