application.match with multi-dimensional arrays (syntax request)

[Keith R]

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]

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]

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]

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.

 

[Alan Beban]

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