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

• 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 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 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")
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 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 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 Tuesday, November 10, 2015 4:15 PM
Monday, November 9, 2015 9:52 PM

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

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

Tuesday, November 10, 2015 7:30 PM