K
Keith R
Sorry for the subject line, I really don't know what to call this...
I'm using the following to assign a range to a variant array in XL2003/WinXP
Option Base 1
Public ExpediteArrayShort As Variant '(1 To x rows, 1 column)
Sub Mysub
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
ExpediteArrayShort = Sheet4.Range("B1:B" &
CStr(Expedite_LastRow)).Value
End sub
This brings my one column of data in, which is great. However, I need to be
able to use Application.Match against this array, which I believe requires a
1-D array.
I tried syntax variants like
FoundRow = Application.Match(sArray(1, i), ExpediteArrayShort(, 1),
False)
but I get "syntax error" (runtime), and the following gives me a runtime
error 9, subscript out of range:
If IsError(Application.Match(sArray(1, i), ExpediteArrayShort(),
False)) Then
presumably because I haven't specified the second parameter.
Notably, msgbox ExpediteArrayShort(3) returned an error, but
ExpediteArrayShort(3,1) returned the third value in the range. If Excel is
going to see this as a 2-D range, is there a way to (easily) use
Application.Match against this range, or am I forced to loop through and
assign all the values one at a time to a 1D array?
Is there a way to redim preserve this array to turn it into a 1-D array, or
a different way to grab it up front to force it into a 1-D array from the
start (other than looping)?
Thanks!
Keith
I'm using the following to assign a range to a variant array in XL2003/WinXP
Option Base 1
Public ExpediteArrayShort As Variant '(1 To x rows, 1 column)
Sub Mysub
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
ExpediteArrayShort = Sheet4.Range("B1:B" &
CStr(Expedite_LastRow)).Value
End sub
This brings my one column of data in, which is great. However, I need to be
able to use Application.Match against this array, which I believe requires a
1-D array.
I tried syntax variants like
FoundRow = Application.Match(sArray(1, i), ExpediteArrayShort(, 1),
False)
but I get "syntax error" (runtime), and the following gives me a runtime
error 9, subscript out of range:
If IsError(Application.Match(sArray(1, i), ExpediteArrayShort(),
False)) Then
presumably because I haven't specified the second parameter.
Notably, msgbox ExpediteArrayShort(3) returned an error, but
ExpediteArrayShort(3,1) returned the third value in the range. If Excel is
going to see this as a 2-D range, is there a way to (easily) use
Application.Match against this range, or am I forced to loop through and
assign all the values one at a time to a 1D array?
Is there a way to redim preserve this array to turn it into a 1-D array, or
a different way to grab it up front to force it into a 1-D array from the
start (other than looping)?
Thanks!
Keith