With Bernie's change you are no longer matching against an array, which I
thought you wanted. In any case I used your code and still had no problem
using Match against 10000 row by 1 column 2D array, not range.
--
Jim
| 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
|
| | >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
| >
| >
| > | >> 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
| >> '----------------------------------------------------------------------
| >>
| >>
| >> | >>>>>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
| >>> | >>> |
| >>> | 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
| >>> |
| >>> |
| >>>
| >>>
| >>
| >>
| >
| >
|
|
