Application.Match on an range defined to a variant array

  • Thread starter Thread starter Keith R
  • Start date Start date
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
 
This worked for me:

Dim v As Variant
Dim vCopy As Variant
v = Range("TwoColRng")
vCopy = v
ReDim Preserve vCopy(LBound(v, 1) To UBound(v), 0 To 0)

Debug.Print Application.Match("a", vCopy, 0)
 
Keith said:
. . .
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.
Click to expand...

Nope. it requires a single "row" or single "column".

Alan Beban
 
This worked in xl2003 for me:

Option Explicit
Sub Mysub()
Dim res As Variant
Dim myArr As Variant
Dim myStr As String

'add some test data
With ActiveSheet.Range("a1:a10")
.Formula = "=cell(""address"",a1)"
myArr = .Value
End With

myStr = "$A$7"

res = Application.Match(myStr, myArr, 0)

If IsError(res) Then
MsgBox "not found"
Else
MsgBox res
End If

End Sub

Maybe your =match() didn't find a match.




Keith said:
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
Click to expand...
 
Tim-

That worked for me too, and now I've learned a little more about the redim
statement as well (figuring out how this worked).

Thank you!
Keith

Tim Zych said:
This worked for me:

Dim v As Variant
Dim vCopy As Variant
v = Range("TwoColRng")
vCopy = v
ReDim Preserve vCopy(LBound(v, 1) To UBound(v), 0 To 0)

Debug.Print Application.Match("a", vCopy, 0)

--
Tim Zych
SF, CA

Keith R said:
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
Click to expand...
Click to expand...
 
Keith R wrote:. . .If Excel
For a single column or single row 2-d array, just use Application.Match
directly; works fine.

For a single-column Variant() array that's not too large (less than
65537 elements in current versions, 5461 elements in earlier versions)

arr = Application.Transpose(arr) will convert it to a 1-based 1-D array.

Alan Beban
 
Back
Top