ddualba
01-05-2017, 02:10 PM
Greetings,
Im trying to explore my understanding of arrays and loading them up with excel table data.
I can create a single column array and put a table column in it (as well as display it) like below
Arr1 = Range("myData[Name]")
tblRows = myTable.DataBodyRange.Rows.Count
For i = 1 to tblRows
Debug.Print Arr1(i, 1)
Next i
My problem is when I want to create a multidimensional array and put several columns in them (The columns are non adjacent)
Sub TableColumnsToArray()
Dim Arr1() As Variant 'my array, currently has no size
Dim dataSheet As Worksheet
Dim myTable As ListObject
Dim tblRows As Long
Dim i As Integer
Set dataSheet = ThisWorkbook.Worksheets("Data_Table")
Set myTable = dataSheet.ListObjects("myData")
tblRows = myTable.DataBodyRange.Rows.Count
Arr1 = Range("myData[Name],myData[Attribute5]") ' This line does not receive the debug error, but could be the source of the problem
For i = 1 To tblRows
Debug.Print Arr1(i, 1) & " : "; Arr1(i, 2) ' This row receives a subscript out of range error
Next i
I might perhaps need a ReDim statement to properly size the array for this to work. Not sure whats wrong and whats possible. I am able to use 2 separate arrays, 1 for each column and get the results, but not sure if this is the way it has to be done. I have several more columns from the same table I want to add to the array and I dont want to have an array for each column.
Any help is appreciated. Thanks
Im trying to explore my understanding of arrays and loading them up with excel table data.
I can create a single column array and put a table column in it (as well as display it) like below
Arr1 = Range("myData[Name]")
tblRows = myTable.DataBodyRange.Rows.Count
For i = 1 to tblRows
Debug.Print Arr1(i, 1)
Next i
My problem is when I want to create a multidimensional array and put several columns in them (The columns are non adjacent)
Sub TableColumnsToArray()
Dim Arr1() As Variant 'my array, currently has no size
Dim dataSheet As Worksheet
Dim myTable As ListObject
Dim tblRows As Long
Dim i As Integer
Set dataSheet = ThisWorkbook.Worksheets("Data_Table")
Set myTable = dataSheet.ListObjects("myData")
tblRows = myTable.DataBodyRange.Rows.Count
Arr1 = Range("myData[Name],myData[Attribute5]") ' This line does not receive the debug error, but could be the source of the problem
For i = 1 To tblRows
Debug.Print Arr1(i, 1) & " : "; Arr1(i, 2) ' This row receives a subscript out of range error
Next i
I might perhaps need a ReDim statement to properly size the array for this to work. Not sure whats wrong and whats possible. I am able to use 2 separate arrays, 1 for each column and get the results, but not sure if this is the way it has to be done. I have several more columns from the same table I want to add to the array and I dont want to have an array for each column.
Any help is appreciated. Thanks