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