Answered by:
How use getupperbound and how assign elements to array?

Question
-
Could you tell me how to do what I'm trying to do? Here is my code. I have an error in the bold lines for getupperbound and below that where I try to assign elements to an array.
Sub GenDataArray()
' Generate an array of the values in the Data tab.
Dim sht1 As Worksheet
Dim LastCell As String
Dim LastRow As Integer
Dim DataAry As Variant
Set sht1 = Sheets("Data")
LastCell = sht1.UsedRange.SpecialCells(xlLastCell).Address
LastRow = sht1.UsedRange.SpecialCells(xlLastCell).Row
' Copy data from data tab.
' cell(2,1) includes the column headings in the Data tab.
DataAry = sht1.Range(Cells(2, 1), LastCell).Value
Dim sht2 As Worksheet
Dim AryRowN As Integer
Dim indx As Integer
Dim Tbl2Ary As Variant
' These are the 27 columns from DataArray that are used in "Table 2" in this order.
Dim Tbl2ColsAry As Variant
Tbl2ColsAry = Array(1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45)
Set sht2 = Sheets("Sheet1")
For rowindx = 0 To DataAry.getupperbound
For colindx = 0 To Tbl2ColsAry.getupperbound
AryColN = Tbl2ColsAry(colindx)
Tbl2Ary(rowindx, colindx) = DataAry(rowindx, AryColN)
Next colindx
Next rowindx
sht2.Cells(1, 1) = Tbl2Ary
End Sub
Monday, November 9, 2015 7:36 PM
Answers
-
The upper bound of an array is returned by the function UBound.
For a one-dimensional array ary, the upper bound is UBound(ary).
For a two-dimensional array ary (such as the values of a range of cells), UBound(ary, 1) returns the upper bound of the rows dimension, and UBound(ary, 2) the upper bound of the columns dimension.
Notes:
1) Even if a range consists of only one row or one column, its array of values is still two-dimensional.
2) The lower bound of the array of values of a range is 1 in both dimensions, not 0.
So
For rowindx = 0 To DataAry.getupperbound
should be
For rowindx = 1 To UBound(DataAry, 1)
Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Marked as answer by VBApractice Tuesday, November 10, 2015 4:15 PM
Monday, November 9, 2015 9:52 PM -
But DataAry is a variant, not a range:
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value 'Variant array
Note that Cells(1,2) is on the activesheet, not necessarily sht1 - thus the added bold sht1
To use a range, the code would need to be
Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell) 'Variant set to Range object
It is also important to note that when using variants, the lower bound could be 0 (the default) or 1 depending on the Option Base setting of the project
For rowindx = LBound(DataAry) To UBound(DataAry)
For colindx = LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry)
If the OP was actually mixing ranges (which can have 0,0 cells - even negatively indexed cells, possibly) and variants, the OP would be better off making sure that this was at the top of the module
Option Base 1
- Marked as answer by VBApractice Tuesday, November 10, 2015 7:01 PM
Tuesday, November 10, 2015 4:30 PM -
You need to dimension that array properly:
Sub GenDataArrayFixed()
' Generate an array of the values in the Data tab.
Dim sht1 As Worksheet
Dim LastCell As String
Dim LastRow As Integer
Dim DataAry As Variant
Dim rowindx As Long
Dim colindx As Long
Dim AryColN As Long
Set sht1 = Sheets("Data")
LastCell = sht1.UsedRange.SpecialCells(xlLastCell).Address
LastRow = sht1.UsedRange.SpecialCells(xlLastCell).Row
' Copy data from data tab.
' cell(2,1) includes the column headings in the Data tab.
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value
Dim sht2 As Worksheet
Dim AryRowN As Integer
Dim indx As Integer
Dim Tbl2Ary As Variant
' These are the 27 columns from DataArray that are used in "Table 2" in this order.
Dim Tbl2ColsAry As Variant
Tbl2ColsAry = Array(1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45)
Set sht2 = Sheets("Sheet1")
ReDim Tbl2Ary(LBound(DataAry) To UBound(DataAry), LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry))
For rowindx = LBound(DataAry) To UBound(DataAry)
For colindx = LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry)
AryColN = Tbl2ColsAry(colindx)
Tbl2Ary(rowindx, colindx) = DataAry(rowindx, AryColN)
Next colindx
Next rowindx
sht2.Cells(1, 1) = Tbl2Ary
End Sub- Marked as answer by VBApractice Tuesday, November 10, 2015 6:54 PM
Tuesday, November 10, 2015 4:54 PM -
Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)
sets the variable DataAry to the range from A2 to LastCell.
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)
sets the variable DataAry to the array of values of that range. The latter line is equivalent to
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value
since Value is the default property of a range.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Marked as answer by VBApractice Tuesday, November 10, 2015 6:58 PM
Tuesday, November 10, 2015 5:10 PM
All replies
-
The upper bound of an array is returned by the function UBound.
For a one-dimensional array ary, the upper bound is UBound(ary).
For a two-dimensional array ary (such as the values of a range of cells), UBound(ary, 1) returns the upper bound of the rows dimension, and UBound(ary, 2) the upper bound of the columns dimension.
Notes:
1) Even if a range consists of only one row or one column, its array of values is still two-dimensional.
2) The lower bound of the array of values of a range is 1 in both dimensions, not 0.
So
For rowindx = 0 To DataAry.getupperbound
should be
For rowindx = 1 To UBound(DataAry, 1)
Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Marked as answer by VBApractice Tuesday, November 10, 2015 4:15 PM
Monday, November 9, 2015 9:52 PM -
Thank you for your explanation and correction. Your code works.
Now I'm getting Run-time error '13': Type mismatch on this line.
Tbl2Ary(rowindx, colindx) = DataAry(rowindx, arycoln)
I get the same error this way.
Tbl2Ary(1, 0) = DataAry(1, 1)
Both arrays are Dim ... As Variant.
Tuesday, November 10, 2015 4:25 PM -
But DataAry is a variant, not a range:
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value 'Variant array
Note that Cells(1,2) is on the activesheet, not necessarily sht1 - thus the added bold sht1
To use a range, the code would need to be
Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell) 'Variant set to Range object
It is also important to note that when using variants, the lower bound could be 0 (the default) or 1 depending on the Option Base setting of the project
For rowindx = LBound(DataAry) To UBound(DataAry)
For colindx = LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry)
If the OP was actually mixing ranges (which can have 0,0 cells - even negatively indexed cells, possibly) and variants, the OP would be better off making sure that this was at the top of the module
Option Base 1
- Marked as answer by VBApractice Tuesday, November 10, 2015 7:01 PM
Tuesday, November 10, 2015 4:30 PM -
I think you're saying that if I don't add sht1.cells that the code could pull data from the active sheet instead of sht1 so I have added this to my code. I also added the option base 1 and changed my lower bound to 1 in the for loop.
I don't understand yet whether I should change this
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value
to this
Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)
Do both options take values from a range on a spreadsheet and put them in an array? What is the difference?
Thanks for your help.
Tuesday, November 10, 2015 4:47 PM -
You need to dimension that array properly:
Sub GenDataArrayFixed()
' Generate an array of the values in the Data tab.
Dim sht1 As Worksheet
Dim LastCell As String
Dim LastRow As Integer
Dim DataAry As Variant
Dim rowindx As Long
Dim colindx As Long
Dim AryColN As Long
Set sht1 = Sheets("Data")
LastCell = sht1.UsedRange.SpecialCells(xlLastCell).Address
LastRow = sht1.UsedRange.SpecialCells(xlLastCell).Row
' Copy data from data tab.
' cell(2,1) includes the column headings in the Data tab.
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value
Dim sht2 As Worksheet
Dim AryRowN As Integer
Dim indx As Integer
Dim Tbl2Ary As Variant
' These are the 27 columns from DataArray that are used in "Table 2" in this order.
Dim Tbl2ColsAry As Variant
Tbl2ColsAry = Array(1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45)
Set sht2 = Sheets("Sheet1")
ReDim Tbl2Ary(LBound(DataAry) To UBound(DataAry), LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry))
For rowindx = LBound(DataAry) To UBound(DataAry)
For colindx = LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry)
AryColN = Tbl2ColsAry(colindx)
Tbl2Ary(rowindx, colindx) = DataAry(rowindx, AryColN)
Next colindx
Next rowindx
sht2.Cells(1, 1) = Tbl2Ary
End Sub- Marked as answer by VBApractice Tuesday, November 10, 2015 6:54 PM
Tuesday, November 10, 2015 4:54 PM -
Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)
sets the variable DataAry to the range from A2 to LastCell.
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)
sets the variable DataAry to the array of values of that range. The latter line is equivalent to
DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value
since Value is the default property of a range.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Marked as answer by VBApractice Tuesday, November 10, 2015 6:58 PM
Tuesday, November 10, 2015 5:10 PM -
The real difference is that when you use the first line, the data is read into memory all at once: with the second, the code ends up reading the range values cell by cell, which may be slower. How you step through is different too - you would use 1 to DataAry.Rows.Count and 1 to DataAry.Columns.Count when you use the range instead of LBound and UBound
- Edited by Bernie Deitrick, Excel MVP 2000-2010 Tuesday, November 10, 2015 6:56 PM
Tuesday, November 10, 2015 6:08 PM -
Many thanks for your help.Tuesday, November 10, 2015 6:59 PM
-
Using the debug data watch, I get the values of the array using the second line. The first line gives me information about the range, but not the values. I'm not sure how to use the first line.Tuesday, November 10, 2015 7:04 PM
-
Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)
For rowindx = 1 To DataAry.Rows.Count
For colindx = LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry)
AryColN = Tbl2ColsAry(colindx)
Tbl2Ary(rowindx -1, colindx) = DataAry(rowindx, AryColN)Next
Next
- Edited by Bernie Deitrick, Excel MVP 2000-2010 Tuesday, November 10, 2015 7:30 PM
Tuesday, November 10, 2015 7:30 PM