Dim i as Long, res as Variant
.. . .
For i = 1 to 600
myVal = cells(i,1)
res = Application.Match(myVal,myArray,0)
if not iserror(res) then
' cell matches a value in myarray
else
' cell doesn't match a value in myarray
end if
next i

or just check the values
Dim rng as Range, i as Long, cnt as Long
Dim res as Variant, rng1 as Range
.. . .
set rng = Range("A1:A600")
for i = lbound(myarray) to ubound(myArray)
cnt = Application.Countif(rng,myarray(i)
if cnt 0 then
' there is a match
' if you need to know which cell
res = Application.Match(myarray(i),rng,0)
set rng1 = rng(res)
msgbox "Match at cell " & rng1.Address
end if
Next

--
Regards,
Tom Ogilvy



"WhytheQ" wrote in message
oups.com...
I have an array of 40 items.
I need to go down a column of 600 cells and for each cell I need to
test whether the cell.value is equel to any of the items in the array.
At present this is really slow as my code is doing a loop of 40, 600
times (i.e 24,000).
The loop looks something like:

For i= 1 to 600
myVal = cells(i,1)
For j = 1 to 40
if myVal = myArray(j) then blah blah
next j
next i

Would I have been better using a collection rather than an array?
Is an array the best choice?

Any help greatly appreciated,
J