A range object is actually an array of cell objects, which has two dimensions: rows and columns.
Making the variant = to the range results in the 2D array. Try

Set MyMatchRange = Range("B1:B10000")

Then MyMatchRange will be a range object, and not an array - MyMatchRange(4000) will work (really,
it could be MyMatchRange.Cells(4000.1).Value - but Excel gives a lot of flexibility in addressing
range object values) but of course, LBound and UBound won't work - 1 to MyMatchRange.Cells.Count
would work.

HTH,
Bernie
MS Excel MVP


"ker_01" wrote in message ...
The problem is that as a variant array pulling in a 1-column range, it ends up creating the actual
array in 2D, e.g. the result is:
MyMatchRange(1 to 10000,1 to 1). I'm not sure why my results are different than the one you
posted- my actual code will be posted below this message in case there is something else I'm doing
wrong.

I confirmed via debug.print that with my current code, MyMatchRange(4000) returning an error,
whereas debug.print MyMatchRange(4000,1) returns the expected value. Also, the following:

Debug.Print LBound(MyMatchRange, 1) 'returns a value of 1
Debug.Print UBound(MyMatchRange, 1) 'returns a value of 10000
Debug.Print LBound(MyMatchRange, 2) 'returns a value of 1
Debug.Print UBound(MyMatchRange, 2) 'returns a value of 1
Debug.Print LBound(MyMatchRange, 3) 'errors out with a subscript out of range error
Debug.Print UBound(MyMatchRange, 3) 'so it never gets to this line, but I'm sure it would also
give the same error

I have yet to find a syntax for Match that will allow me to specify the match against the first
dimension of a multi-dimensional array, so I'm stuck unless there is a way to do this with Match
(against a 2D array), a way to redim preserve the array to 1D, or I go back to looping the data
into my array.

During processing, the contents of the array and of the worksheet may be changed/updated
independently, so I can't rely on just using Match against the original worksheet values.

Thanks,
Keith

Actual code, in 2 different modules (not in worksheets)
Note that while in my original post I simplified it to "MyMatchRange", the real variant array is
called "MyMatchRange1subArray" (this is one of several ranges and subarrays)
'----------------------------------------------------------------------
Public MyMatchRange_LastRow As Integer
Public MyMatchRange1subArray As Variant

Sub MyCodeSnippetForLoadingTheDataIntoAnArray
MyMatchRange_LastRow = Find_Last(Sheet14)
MyMatchRange1subArray = Sheet14.Range("B1:B" & CStr(MyMatchRange_LastRow)).Value
'I confirmed the 2D nature of the array here, right after it is created- per my notes above
(debug.print)
End Sub
'----------------------------------------------------------------------

Function Find_Last(sht As Worksheet)
' seems to be working fine- returns the correct number of rows
Find_Last = sht.Cells.Find(What:="*", After:=sht.Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
End Function
'----------------------------------------------------------------------

'In a different module...

Function TranslateMe(ComparisonArrayValue As String) As Variant
' other code
subTXValue = Application.Match(ComparisonArrayValue , MyMatchRange1subArray, False)
'other code
End Function
'----------------------------------------------------------------------


"Jim Rech" wrote in message ...
is there any way to redim (preserve) my single-column, 2D array data into
a 1D array
for use with the Application.Match function?

I don't see the problem:

Sub GrabMyData()
Dim MyMatchRange As Variant
MyMatchRange = Sheet1.Range("B1:B10000")
MsgBox Application.Match("abc", MyMatchRange, False)
End Sub

"abc" is in cell B4 and the Msgbox returns 4.

--
Jim
"ker_01" wrote in message
...
|
| I create arrays of data by declaring a variant data type and setting it
| equal to sheet ranges. For later data crunching, I have several ranges
that
| I need to use with Application.Match to find certain values. However, my
| method of copying a range into a variant (as an array) brings this data in
| as a 2D array, even though it is only one column of data.
|
| Other than looping the data into a new 1D array (at which point, I might
as
| well just populate that 1D array directly from the sheet itself), is there
| any way to redim (preserve) my single-column, 2D array data into a 1D
array
| for use with the Application.Match function?
|
| Or alternatively, is there syntax for the Application.Match function that
| will allow me to test the match against a selected parameter of a 2D (or
3D)
| array?
|
| I've looked online and googled, but haven't found any solutions.
| Thanks!!
| Keith
|
|
| Sample aircode:
|
| Sub GrabMyData
| Dim MyMatchRange as Variant
| MyMatchRange = Sheet1.range("B1:B10000")
|
| 'possibly redim to 1D here-
| 'redim preserve (MyMatchRange(1 to 10000) but redim doesn't allow me to
| change the number of dimensions :(
| 'from help: If you use the Preserve keyword, you can resize only the
last
| array dimension and you can't change the number of dimensions at all
| 'attempts to redim to 1D without the preserve keyword appear to erase
all
| data in the array
|
| '...lots of intermediate code...
|
| Application.match (SampleID,MyMatchRange,False)
| 'or Application.match (SampleID,MyMatchRange(?,1),False), or some way to
| process within a 2D array?
|
| End sub
|
|