Results 1 to 6 of 6

Thread: [RESOLVED] Multiple dynamic tables VBA

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2021
    Posts
    7

    Resolved [RESOLVED] Multiple dynamic tables VBA

    I am a beginner when it comes to VBA. I want to create as many dynamic table as a given value X. The tables should be created one near the other. Each row of the table should have at least a cell with text and a formula. This is what I have done already:

    Private Sub CommandButton1_Click()

    Dim t As ListObject

    Dim c As Range


    Sheet4.Activate

    Set t = Sheet4.ListObjects.Add(xlSrcRange, Sheet4.Range("A2").Resize(37, 5), , xlNo)

    t.ListColumns(1).Name = "Samples"

    t.ListColumns(2).Name = "Activities"

    t.ListColumns(3).Name = "Discipline"

    t.ListColumns(4).Name = "Activity"

    t.ListColumns(5).Name = "Duration(h)"

    End Sub

    I don't know how can I make the loop to put the tables on columns, not rows and how to add names and formulas in the table rows dynamically.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Multiple dynamic tables VBA

    I don't know how can I make the loop to put the tables on columns, not rows and how to add names and formulas in the table rows dynamically.
    i am not sure from this what you actually want to do. though i am sure it is not too difficult to achieve

    you can do a loop like
    Code:
    for rw = 2 to t.listrows.count  ' row 1 is the header row
        for col = 1 to t.listcolumns.count
            t.range(rw,col) = "xx"
        next
    next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2021
    Posts
    7

    Re: Multiple dynamic tables VBA

    I need a loop that will display more than one table. For example a loop that goes from 1 to 3 that displays 3 identical tables, starting from A2 to M2. The only thing that should be different is the Duration(h) column where different values are substracted or added.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Multiple dynamic tables VBA

    like
    Code:
    For i = 1 To 3
        Set t = Sheet4.ListObjects.Add(xlSrcRange, Sheet4.Range("A2").Offset(37 * (i - 1) + 3 * i).Resize(37, 5), , xlNo)
    Next
    you might have to adjust the math a bit
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2021
    Posts
    7

    Re: Multiple dynamic tables VBA

    Also, instead of 3 I have to get the value from TextBox3 which is in Sheet2, not Sheet4.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Multiple dynamic tables VBA

    Code:
    for i = 1 to sheet2.textbox3.text
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width