Woohoo! That was the trick- the match statement now works!

ThankYouThankYouThankYouThankYouThankYouThankYou

I would have thought that the 'set' statement essentially created a named
range, setting the MyMatchRange as a reference to the real range. I tested,
and (much to my happiness) after the set statement I can change the value of
'abc' to 'xyz', then run the match statement and still get the result of 4
(the position 'abc' used to be). So the 'set' statement really passes a copy
of the range, rather than setting a reference? That isn't intuitive to me,
but I'm glad it appears to work that way!

Thanks again- I'm too embarrased to say how much time I've wasted trying to
get this array and match statement to play together.

Best,
Keith

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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
|
|