Results 1 to 7 of 7

3d array

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    615

    3d array

    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!
    Last edited by brucemc777; 04-30-2016 at 03:48 PM. Reason: ReDim line of code, now using "1 to"

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  2. [SOLVED] Populate one array from another array and print new array as a range
    By Kaden265 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2014, 07:52 AM
  3. Loop new messages containing a table, populate a dynamic array, paste array to Excel
    By laripa in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2013, 07:20 AM
  4. [SOLVED] Quick Array question - Copy array to another array then resize?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-02-2013, 01:17 AM
  5. [SOLVED] Populate Listbox with all rows of a dynamic array where elements of a single array match.
    By Tayque_J_Holmes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-07-2012, 04:54 AM
  6. Single Conditional Array x two Multi-Column Array - Approach needed
    By David Brown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2010, 11:41 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1