-
Mar 29th, 2021, 03:21 AM
#1
Thread Starter
New Member
[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.
-
Mar 29th, 2021, 04:56 AM
#2
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
-
Mar 29th, 2021, 05:41 AM
#3
Thread Starter
New Member
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.
-
Mar 29th, 2021, 06:27 AM
#4
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
-
Mar 29th, 2021, 08:44 AM
#5
Thread Starter
New Member
Re: Multiple dynamic tables VBA
Also, instead of 3 I have to get the value from TextBox3 which is in Sheet2, not Sheet4.
-
Mar 30th, 2021, 02:51 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|