+ Reply to Thread
Results 1 to 2 of 2

Cycle through all records in a table listobject

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Cycle through all records in a table listobject

    Hi. I have a table listobject in my spreadsheet called TableSource. It has six columns called SheetNum, Name, Table, Depot, Area, and FieldSource.

    In my VBA I have a type class
    Type ChosenData
        name As String
        table As String
        depot As String
        area As String
        fieldSource As String
    End Type
    Is there an easy way to run a sub that will cycle through all rows in TableSource and add the data to an array of the ChosenData type.

    This is what I have so far:
    Dim workings As Worksheet
    Set workings = ActiveWorkbook.Worksheets("workings")
    Dim myData(2) As ChosenData
    
    With workings
        myData(0).name = .Range("C4")
        myData(1).name = .Range("C5")
        myData(2).name = .Range("C6")
    
        myData(0).table = .Range("D4")
        myData(1).table = .Range("D5")
        myData(2).table = .Range("D6")
    
        myData(0).depot = .Range("E4")
        myData(1).depot = .Range("E5")
        myData(2).depot = .Range("E6")
    
        myData(0).area = .Range("F4")
        myData(1).area = .Range("F5")
        myData(2).area = .Range("F6")
    
        myData(0).fieldSource = .Range("G4")
        myData(1).fieldSource = .Range("G5")
        myData(2).fieldSource = .Range("G6")
    End With
    The table starts on B3, and only has 3 records. But if I wanted to add a 4th or more, I'd have to edit the code. What's the best way to add all rows to my array type so I won't have to worry about changing the code

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Cycle through all records in a table listobject

    How does this work for you?

    Dim lr As Long
    Dim i as long
    lr = workings.Range("C" & Rows.Count).End(xlUp.Row) 'last row with data in column C
    
    With workings
        For i = 4 To lr '4 seems to be first row with data?
            myData(i - 4).Name = .Range("C" & i)
            myData(i - 4).Table = .Range("D" & i)
            myData(i - 4).depot = .Range("E" & i)
            myData(i - 4).area = .Range("F" & i)
            myData(i - 4).fieldSource = .Range("G" & i)
        Next i
    End With
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Rearrange Table Column (i.e. ListObject.ListColumns...)
    By kestefon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-03-2018, 10:09 AM
  2. [SOLVED] Code help: Update new row of Table (ListObject) via UserForm
    By onmyway in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-27-2015, 04:11 AM
  3. ListObject.ListRows not returning last row of table if header row
    By excel_joe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2014, 04:59 PM
  4. [SOLVED] Get Cursor Location in a ListObject table
    By snapfade in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2014, 05:24 PM
  5. [SOLVED] Converting Query Table to ListObject
    By x10sion in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2013, 06:56 AM
  6. Remove Filter From ListObject Table
    By efernandes67 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2011, 04:47 AM
  7. Table ListObject Filter
    By efernandes67 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2011, 07:40 AM

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