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

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

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

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

I looked a everything I could find different between the simplified code you
provide (which works for me in a new workbook) and the workbook that is
giving me trouble; I added them all to the simplified workbook just in case
any of them might have been a problem (although I didn't predict that any of
them would);
* I removed ".value" from my old assignment of
"Sheet1.range("B1:B10000").value"
* I dimmed the variant array as public instead of within the sub to more
closely match my real code
* I adding option base 1 to the module to match my real code
* changed the range reference in the simplified workbook to be
pseudo-dynamic: Sheet1.range("B1:B" & cstr(10000)) to resemble the real code

everything still works fine in the new workbook- I can't get it to throw as
a 2D array like my main workbook. As far as I can tell, other than the
actual data in the real worksheet, nothing else is different...

Now I'm really confused. :(

Thanks,
Keith

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

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

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

My apologies- that's a function of my poor communication skills- what I
really needed was to get match working, but I was already (overly) focused
on the array solution because I thought I was 90% of the way there. Given
that you were able to get the simplified code to match against a 2D array, I
probably was close, but just never figured out where I was messing up the
last 10%. Just to educate myself, I tried again:

Starting with the working set statement I added a second fake array using
the old syntax;
Set MyMatchRange = Sheet14.Range("B1:B10000")

MyMatchRange2 = Sheet14.Range("B1:B10000")

then in the now-working match procedure I tried the new array;
'TestValue = an entry that exists in the real data
debug.print Application.Match(TestValue, MyMatchRange, False)
debug.print Application.Match(TestValue, MyMatchRange2, False)
debug.print Application.Match(TestValue, MyMatchRange2(), False)

The first one returns the correct row, but I get a 2042 error printed on the
second match statement. I tried the third line in case it needed the
parantheses (since my syntax is sub-par) and it gave me a subscript out of
range msgbox (no result printed in the immediate window). Either way, I'm
good for now with a working match, but I'm eager to learn so if I can get
both ways working, that will give me a better skill set for future projects.

Best,
Keith

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

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