Reply to thread

Message: [QUOTE="Jim Rech, post: 6336233"] But, if you're still there, Matching against a range is far faster than against an array. -- 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 | >>> | | >>> | | >>> | >>> | >> | >> | > | > | | [/QUOTE]

Verification