Fast record searching in array

[Christopher Panadol]

Hi,

I know that there is the SEARCH command to find a record location quickly in
a range of cells. However I would like to know whether it has the same
command to find record location in array.

I have searched a lot of information in many VBA sites. They all suggest to
use a looping method like FOR....NEXT, DO....WHILE, etc to achieve this.
However, I found that if the quantity of array record is over serveral
thousand, using the looping method to locate the record is very slow.

I know it is work by using the temorary sheet rather than array for such
case. I also test it and it found quite slower than using the array method.

Is there a command or otherwise to have a fast searching in array?

Regards,
Chris

 

[Tim Williams]

Sub tester()

Dim a, r
a = Array(1, 3, 2, 4, 5, 6)
r = Application.Match(3, a, 0)

If Not IsError(r) Then
MsgBox r
Else
MsgBox "Not found"
End If


End Sub


Tim

 

[Dave Peterson]

Maybe...

Is it a one dimensional array?

dim myArr as Variant
dim res as variant
myArr = array(1,3,6,7)
res = application.match(6,myarr,0)
if iserror(res) then
msgbox "no match"
else
msgbox "match at: " & res
end if

 

[Christopher Panadol]

Thanks for your solution.

But what if not one dimensional array but I would like to find the
information in the nth dimension?

 

[Peter T]

Is the array sorted, in particular the dimension you want to look in. If so
a "binary search" will return your result almost instantly no matter what
the size of your array. It's worth keeping your array sorted just for for
this purpose.

A quick google should find plenty of examples for you.

Regards,
Peter T

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim WhichCol As Variant
Dim arr As Variant
Dim res As Variant
Dim wks As Worksheet

'Fill the array with test data
Set wks = Worksheets.Add
With wks.Range("a1:g30")
.Formula = "=cell(""address"",a1)"
arr = .Value
End With

WhichCol = 4

With Application
res = .Match("$D$7", .Index(arr, 0, WhichCol), 0)
End With

If IsError(res) Then
MsgBox "no match"
Else
MsgBox "match found: " & res
End If

End Sub