But, if you're still there, Matching against a range is far faster than
against an array.

--
Jim
"ker_01" wrote in message
...
| 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
| |
| |
|
|
|
|
|
|
|
|