I am attempting to read (designated/marked pairs of string values) from a number of (tables on one sheet) into an array.
The number of tables on the sheet changes over time.
The number of rows in each table changes over time.
The number of columns in each table is fixed at two, and when the first of the two columns is marked the items in that row constitute the pair of values.
The marker for the information from the cells that I want read into the array from each table is that the first cell in each needed row will have a comment created/attached to it.
Everything in the following blows up (fails) at the point: arReport(intTblCtr) = tbl.name
tbl.Name is correct, but obviously my handling of assigning values to an array isn't.
I have more explanation of what I intend to do in the comments of the following code.
I am probably mis-using the word "parameter" in my attempt to describe the three "positions" (labeled immediately hereafter: "A", "B" and "C": array(A,B,C) ) within the entire array.
Sub FUHistoryReport()
Dim intCtr As Integer
Dim intTblCtr As Integer
Dim intRowCtr As Integer
Dim arReport() As String
Dim tbl As ListObject
intTblCtr = 0
intRowCtr = 0
For Each tbl In Worksheets("FUHistory").ListObjects
intTblCtr = intTblCtr + 1
If tbl.DataBodyRange.Rows.Count > intRowCtr Then intRowCtr = tbl.DataBodyRange.Rows.Count
Next tbl
ReDim arReport(1 To intTblCtr, 1 To intRowCtr, 1 To 2) As String
intTblCtr = 0
intRowCtr = 0
For Each tbl In ActiveSheet.ListObjects
intTblCtr = intTblCtr + 1
arReport(intTblCtr) = tbl.Name 'For each Table I want to assign the name of the table to the array's first parameter
For intRowCtr = 1 To tbl.DataBodyRange.Rows.Count
arReport(intTblCtr, intRowCtr) = tbl.DataBodyRange.Row 'For each row number in the Table being read in, I want to
'assign the row number (of the databodyrange) into the second
'array parameter
If Not tbl.DataBodyRange.Cells(intRowCtr, 1).Comment Is Nothing Then 'And each time the first column's cell has a comment,
'I want to assign the first column's value to the first
'of the third parameter's items and the second column
'to the second of the third column's parameters...
arReport(intTblCtr, intRowCtr, 1) = tbl.DataBodyRange.Cells(intRowCtr, 1).Text
arReport(intTblCtr, intRowCtr, 2) = tbl.DataBodyRange.Cells(intRowCtr, 2).Text
End If
Next intRowCtr
Next tbl
Stop
End Sub
Help?!?
I did my best to break everything down for I know sometimes I post requests with presumed knowledge of what I am trying to do swimming around in my head!
Thank-you!
Bookmarks