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)

--
Tim Zych
SF, CA

"Keith R" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Keith R wrote:
> . . .
> 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.

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 R wrote:
>
> 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

--

Dave Peterson

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" <tzych@NOSp@mE@RTHLINKDOTNET> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>

Keith R wrote:. . .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?
>>>

For a single column or single row 2-d array, just use Application.Match
directly; works fine.

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

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