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