Fastest way to find item in an array.

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

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


---
Regards,
Norman



"WhytheQ" <[email protected]> wrote in message
news:[email protected]...
>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
>

> 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...
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"WhytheQ" <[email protected]> wrote in message
news:[email protected]...
>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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

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

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

"WhytheQ" <[email protected]> wrote in message
news:[email protected]...
> 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
>