Fastest way to find item in an array.

[WhytheQ]

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

 

[Norman Jones]

Hi J,

Try something like:

For i = 1 To 600
With Cells(i, 1)
If Not IsError(Application.Match(.Value, myArray, 0)) Then
'do something, e.g:
.Interior.ColorIndex = 6
End If
End With
Next i

 

[Dana DeLouis]

Would I have been better using a collection rather than an array?

My personal choice would be a "Dictionary" object, as it has an "Exists"
method.

It would look something like the following instead of a loop.

If Dic.Exists(MyItem) then...

 

[WhytheQ]

Thanks Norman/Dana, I'll experiment with both of your ideas.
Regards
Jason

 

[Tom Ogilvy]

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

 

[Charles Williams]

Try getting the 600 into an array

dim myVals as variant
myVals=the600Cells
For i= 1 to ubound(myVal,1)
For j = 1 to 40
if myVals(i,1) = myArray(j) then blah blah EXIT FOR
next j
next i

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[WhytheQ]

Charles,
Thanks for contribution but I think your code is still potentially
going to be looping round i 600 times and for each i there will be 40
j's : in other words 24,000 executions.
Looking at other posts the below code:
For j = 1 to 40
if myVals(i,1) = myArray(j) then blah blah EXIT FOR
next j
could be replaced by:
If Not IsError(Application.Match(.Value, myArray, 0)) Then
'do something
End If
and might be a bit quicker

anyway....thanks everyone!loads of ideas
J

 

[Peter T]

You might look carefully again at Charles' suggestion in full, in particular
first putting cell values into an array rather than looping cells.

dim myVals as variant
myVals=the600Cells

Would need to test but I'd be surprised if your Match function works faster
than looping the array.

If you are repetitively searching the same array better first to sort it,
then there are faster methods for searching it.

Regards,
Peter T