Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching a row ( record ) | Excel Discussion (Misc queries) | |||
Fast way to add two array in VBA | Excel Programming | |||
searching a table - fast! | Excel Programming | |||
range to VBA array, and doing this fast | Excel Programming | |||
Remove First Row from Variant Array FAST? | Excel Programming |