I have two large arrays (which are actually worksheets that have been loaded
into memory for faster processing); I cycle through every 'row' in the first
array to get a source value, then I cycle through every value in one
'column' of the second array to find /every/ match of that value.

Due to the size of my arrays, this takes a long time (about 10 minutes). It
seems that if I could use application.match, this could speed things up
considerably.

Can anyone suggest syntax for using application.match against a
multidimensional array, and even better, how to iterate from the last found
match to the end of the array each time?

I'm thinking something like:

Dim SourceArray (1 to 10,000, 1 to 50)
Dim CheckArray (1 to 40,000, 1 to 100)

'load the sheets, then:

For MySourceRow = 1 to 10000
SourceValue = SourceArray (MySourceRow, 14)
FoundMatchRow = Application.Match(SourceValue, CheckArray(?,31),False)
....etc

The two problems I need to overcome are (a) how do I refer to a single
dimension of a multidimensional array (where the questionmark is- match
against all of column 31), and (b) if I find a match, how do I make a
subsequent loop only search for matches from there forward, e.g. if a match
is found in row 27,418, then I want to do another application.match with the
same SourceValue for rows 27,419 through 40,000 for column 31 in my
CheckArray table.

Thanks for any help and advice!
Keith

On Sheet1, I put this in A1:
=cell("address",a1)

I copied down 50 rows and across 10 columns (A1:J50).

Then I used this and it worked ok for me:

Option Explicit
Sub testme()
Dim myArr As Variant
Dim myRow As Variant
Dim myCol As Variant
Dim myColKey As String
Dim myRowKey As String

myArr = Worksheets("sheet1").Range("a1:J50").Value

myRowKey = "$a$37"
myColKey = "$e$1"

With Application
myRow = .Match(myRowKey, .Index(myArr, 0, 1), 0)
myCol = .Match(myColKey, .Index(myArr, 1, 0), 0)
If IsNumeric(myRow) _
And IsNumeric(myCol) Then
MsgBox myArr(myRow, myCol)
Else
MsgBox "at least one missing match"
End If
End With
End Sub

I got $E$37 back and that's what I expected.



Keith R wrote:
>
> I have two large arrays (which are actually worksheets that have been loaded
> into memory for faster processing); I cycle through every 'row' in the first
> array to get a source value, then I cycle through every value in one
> 'column' of the second array to find /every/ match of that value.
>
> Due to the size of my arrays, this takes a long time (about 10 minutes). It
> seems that if I could use application.match, this could speed things up
> considerably.
>
> Can anyone suggest syntax for using application.match against a
> multidimensional array, and even better, how to iterate from the last found
> match to the end of the array each time?
>
> I'm thinking something like:
>
> Dim SourceArray (1 to 10,000, 1 to 50)
> Dim CheckArray (1 to 40,000, 1 to 100)
>
> 'load the sheets, then:
>
> For MySourceRow = 1 to 10000
> SourceValue = SourceArray (MySourceRow, 14)
> FoundMatchRow = Application.Match(SourceValue, CheckArray(?,31),False)
> ....etc
>
> The two problems I need to overcome are (a) how do I refer to a single
> dimension of a multidimensional array (where the questionmark is- match
> against all of column 31), and (b) if I find a match, how do I make a
> subsequent loop only search for matches from there forward, e.g. if a match
> is found in row 27,418, then I want to do another application.match with the
> same SourceValue for rows 27,419 through 40,000 for column 31 in my
> CheckArray table.
>
> Thanks for any help and advice!
> Keith

--

Dave Peterson

Dave- that is a brilliant solution to finding the first match! I've pulled
the .Match(myRowKey, .Index(myArr, 0, 1), 0) apart to understand how it
works, but I'm not clear on how to force the index on subsequent loops to
only check rows that are past the last found match (looking for multiples).
Is it just an index parameter change that I'm not understanding (for part
(b) below)?

Is it possible to put in a range for one of the index values, for example
(using your example) to look for subsequent matches, it could be
..Match(myRowKey, .Index(myArr, 37 to 50, 1), 0)?

Thanks!!
Keith

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sheet1, I put this in A1:
> =cell("address",a1)
>
> I copied down 50 rows and across 10 columns (A1:J50).
>
> Then I used this and it worked ok for me:
>
> Option Explicit
> Sub testme()
> Dim myArr As Variant
> Dim myRow As Variant
> Dim myCol As Variant
> Dim myColKey As String
> Dim myRowKey As String
>
> myArr = Worksheets("sheet1").Range("a1:J50").Value
>
> myRowKey = "$a$37"
> myColKey = "$e$1"
>
> With Application
> myRow = .Match(myRowKey, .Index(myArr, 0, 1), 0)
> myCol = .Match(myColKey, .Index(myArr, 1, 0), 0)
> If IsNumeric(myRow) _
> And IsNumeric(myCol) Then
> MsgBox myArr(myRow, myCol)
> Else
> MsgBox "at least one missing match"
> End If
> End With
> End Sub
>
> I got $E$37 back and that's what I expected.
>
>
>
> Keith R wrote:
>>
>> I have two large arrays (which are actually worksheets that have been
>> loaded
>> into memory for faster processing); I cycle through every 'row' in the
>> first
>> array to get a source value, then I cycle through every value in one
>> 'column' of the second array to find /every/ match of that value.
>>
>> Due to the size of my arrays, this takes a long time (about 10 minutes).
>> It
>> seems that if I could use application.match, this could speed things up
>> considerably.
>>
>> Can anyone suggest syntax for using application.match against a
>> multidimensional array, and even better, how to iterate from the last
>> found
>> match to the end of the array each time?
>>
>> I'm thinking something like:
>>
>> Dim SourceArray (1 to 10,000, 1 to 50)
>> Dim CheckArray (1 to 40,000, 1 to 100)
>>
>> 'load the sheets, then:
>>
>> For MySourceRow = 1 to 10000
>> SourceValue = SourceArray (MySourceRow, 14)
>> FoundMatchRow = Application.Match(SourceValue,
>> CheckArray(?,31),False)
>> ....etc
>>
>> The two problems I need to overcome are (a) how do I refer to a single
>> dimension of a multidimensional array (where the questionmark is- match
>> against all of column 31), and (b) if I find a match, how do I make a
>> subsequent loop only search for matches from there forward, e.g. if a
>> match
>> is found in row 27,418, then I want to do another application.match with
>> the
>> same SourceValue for rows 27,419 through 40,000 for column 31 in my
>> CheckArray table.
>>
>> Thanks for any help and advice!
>> Keith
>
> --
>
> Dave Peterson

You may find it easier to keep the data in the range, then use excel's/VBA's
..Find command to search for what you're using.

I think that the other choice is to offset/resize the array for each subsequent
search. And I'm not sure that you would be saving time that way.

Keith R wrote:
>
> Dave- that is a brilliant solution to finding the first match! I've pulled
> the .Match(myRowKey, .Index(myArr, 0, 1), 0) apart to understand how it
> works, but I'm not clear on how to force the index on subsequent loops to
> only check rows that are past the last found match (looking for multiples).
> Is it just an index parameter change that I'm not understanding (for part
> (b) below)?
>
> Is it possible to put in a range for one of the index values, for example
> (using your example) to look for subsequent matches, it could be
> .Match(myRowKey, .Index(myArr, 37 to 50, 1), 0)?
>
> Thanks!!
> Keith
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > On Sheet1, I put this in A1:
> > =cell("address",a1)
> >
> > I copied down 50 rows and across 10 columns (A1:J50).
> >
> > Then I used this and it worked ok for me:
> >
> > Option Explicit
> > Sub testme()
> > Dim myArr As Variant
> > Dim myRow As Variant
> > Dim myCol As Variant
> > Dim myColKey As String
> > Dim myRowKey As String
> >
> > myArr = Worksheets("sheet1").Range("a1:J50").Value
> >
> > myRowKey = "$a$37"
> > myColKey = "$e$1"
> >
> > With Application
> > myRow = .Match(myRowKey, .Index(myArr, 0, 1), 0)
> > myCol = .Match(myColKey, .Index(myArr, 1, 0), 0)
> > If IsNumeric(myRow) _
> > And IsNumeric(myCol) Then
> > MsgBox myArr(myRow, myCol)
> > Else
> > MsgBox "at least one missing match"
> > End If
> > End With
> > End Sub
> >
> > I got $E$37 back and that's what I expected.
> >
> >
> >
> > Keith R wrote:
> >>
> >> I have two large arrays (which are actually worksheets that have been
> >> loaded
> >> into memory for faster processing); I cycle through every 'row' in the
> >> first
> >> array to get a source value, then I cycle through every value in one
> >> 'column' of the second array to find /every/ match of that value.
> >>
> >> Due to the size of my arrays, this takes a long time (about 10 minutes).
> >> It
> >> seems that if I could use application.match, this could speed things up
> >> considerably.
> >>
> >> Can anyone suggest syntax for using application.match against a
> >> multidimensional array, and even better, how to iterate from the last
> >> found
> >> match to the end of the array each time?
> >>
> >> I'm thinking something like:
> >>
> >> Dim SourceArray (1 to 10,000, 1 to 50)
> >> Dim CheckArray (1 to 40,000, 1 to 100)
> >>
> >> 'load the sheets, then:
> >>
> >> For MySourceRow = 1 to 10000
> >> SourceValue = SourceArray (MySourceRow, 14)
> >> FoundMatchRow = Application.Match(SourceValue,
> >> CheckArray(?,31),False)
> >> ....etc
> >>
> >> The two problems I need to overcome are (a) how do I refer to a single
> >> dimension of a multidimensional array (where the questionmark is- match
> >> against all of column 31), and (b) if I find a match, how do I make a
> >> subsequent loop only search for matches from there forward, e.g. if a
> >> match
> >> is found in row 27,418, then I want to do another application.match with
> >> the
> >> same SourceValue for rows 27,419 through 40,000 for column 31 in my
> >> CheckArray table.
> >>
> >> Thanks for any help and advice!
> >> Keith
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will cycle through the row numbers:

x = Application.Count(ArrayMatch(SourceValue, _
Application.Index(CheckArray, 0, CheckArrayColNo)))
For i = 1 To x / 2
Debug.Print ArrayMatch(SourceValue, CheckArray)(i, 1)
Next

Alan Beban

Dave Peterson wrote:
> You may find it easier to keep the data in the range, then use excel's/VBA's
> .Find command to search for what you're using.
>
> I think that the other choice is to offset/resize the array for each subsequent
> search. And I'm not sure that you would be saving time that way.
>