Fast record searching in array
 

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

Fast record searching in array
 

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


"Christopher Panadol" wrote in message
...
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

Fast record searching in array
 

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 wrote:

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

--

Dave Peterson

Fast record searching in array
 

Thanks for your solution.

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


"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 wrote:

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

--

Dave Peterson

Fast record searching in array
 

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

"Christopher Panadol" wrote in message
...
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

Fast record searching in array
 

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

Christopher Panadol wrote:

Thanks for your solution.

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

"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 wrote:

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

--

Dave Peterson

--

Dave Peterson