Search code examples
excelvbaexcel-2013

Fill Excel Table with VBA Field by Field

I have an Excel table, that I need to fill record by record.

I began a code 

Private Sub AddRecBro_Click()
    Dim Tbl As ListObject
    Dim NewRow As ListRow

    Set Tbl = Worksheets("Broker").ListObjects("Broker")
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

    ....

End Sub

Now I would like to populate this table field by field a bit like this...

Field("ID")=Max(Broker[ID])+1
Field("F1")=Sheets("Dashboard").Range("U5").Value
Field("F2")=Sheets("Dashboard").Range("U6").Value
Field("F3")=Sheets("Dashboard").Range("U7").Value
etccc

I don't know how to populate my list objects this way...Actually I have my data in column and not at the same place... so i would like to select them at their location to Fill up the New record... Thanks

Solution

  • A ListObject table is comprised of a HeaderRowRange (if the table has headers) and a DataBodyRange. Both are Range objects that you can iterate using normal iteration methods for cells:

    Dim r as Long, c as Long

For r = 1 to Tbl.DataBodyRange.Rows.Count
    For c = 1 to Tbl.DataBodyRange.COlumns.Count
        Tbl.DataBodyRange.Cells(r, c).Value = "__Insert some value__"
    Next
Next