K
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
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