Creating a table and fill value by looping

[yxz152830]

Gurus,
i'm trying to fill values in tables created:

Sheets("abc").ListObjects.Add(xlSrcRange, Cells(1000000, 1).End(xlUp).Offset(1, 0), , xlNo).Name = "ss" & day_week & "summary"

first problem is that i cannot use .databodyrange to fill in values by looping like this,error will occur:

with table
for x =1 to matrix
   for y = 1 to matrix2
databodyrange(matrix*matrix2,1) = xxx
next y
next x
end with

i tried filling in the 1st value and found out basically the newly created table does not have databodyrange(1,1) so error occurs.
I also tried go further by typing in a random value in databodyrange(1,1) and see how things will go and it works

my solution would be adding a value in the blanked cell under the header to make it databodyrange(1,1)

how do I put that action in coding? Thanks in advance!

 

[Siddharth Rout]

When you create a table like this, there is no DataBodyRange. If you try using

Msgbox tbl.DataBodyRange.Address

you will get Runtime Error 91: Object variable or With Block variable not set error

At this moment there are no rows in the table. You can check that using

Msgbox tbl.ListRows.Count

Add a row or column(if required) and then add the data. Here is an example

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim LRow As Long
    '~~> Test Value
    Dim day_week As String: day_week = "01"
   
    '~~> This is your worksheet
    Set ws = ThisWorkbook.Sheets("abc")
   
    With ws
        '~~> Find the last row to get the range
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
       
        '~~> Create table
        Set tbl = .ListObjects.Add(xlSrcRange, .Range("A" & LRow), , xlNo)
    End With
   
    With tbl
        '~~> Set the name
        .Name = "ss" & day_week & "summary"
       
        'Debug.Print .ListRows.Count
       
        '~~> Add a new row
        .ListRows.Add
       
        '~~> Add Column if you want to
        '.ListColumns.Add
       
        '~~> Set value
        .DataBodyRange(1, 1) = "YOUR VALUE"
    End With
End Sub

 

[RoryA]

Given that it looks like you already know how many rows you need, size the table appropriately when you create it. That should be a lot faster than adding Listrows. It's probably faster still to populate the range (preferably using an array so you only write once) then convert that to a table when you're done.

 

[Siddharth Rout]

Given that it looks like you already know how many rows you need, size the table appropriately when you create it. That should be a lot faster than adding Listrows.

True that!