Put formulae into listobject

Hello experts

My question is about listobjects.
I'd like to add a formula to the first column of each row.

The first column/row has to be the first day of a month.
Each consecutive row needs to be the previous row + 1 month.

While I have been able to add a formula in the first row,
I don't know how to set the dates for the remaining rows.

Can someone give me a hint?



Sub AddRow()
    Dim wk As Workbook
    Set wk = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wk.ActiveSheet
    Dim intAbholungen As Integer     'Number of rows (=pickups)
    Dim intLetzteAbholung As Integer 'last pickup, used for loop
    Dim ersterMonat As Integer       'first month
    Dim Jahr As Integer              'start year
    Dim oListObject As ListObject
    Set oListObject = ws.ListObjects("pickups")

    'set value to variable
    intAbholungen = ws.Range("Abholungen")
    ersterMonat = ws.Range("ErsterMonat")
    Jahr = ws.Range("DasJahr")
    'Delete Table Rows
    Call DeleteTableRows(oListObject)
    'Add Rows based on number of row numbers (Abholungen, number of pickups)
    For intLetzteAbholung = 1 To intAbholungen - 1
    With oListObject
    End With

    'Put formulae into listobjects
    'first step is to put create a date: first day of a month
    With oListObject

    'Question to EE:
    'How do I add a new formula (a date) to the 1st column of the 1st row

    .Range(2, 1).FormulaR1C1 = "=Date(DasJahr,ersterMonat,1)"

    'Question to EE:
    'New formulae: always add one more month to the previous month
    'loop till the last row

    'Here I chose the second row
    .Range(3, 1).FormulaR1C1 = "DATE(YEAR(A9),MONTH(A9)+1,DAY(A9))"  '?

    End With
End Sub

Open in new window

Massimo ScolaAsked:
Who is Participating?

Just use one formula

    .Range(2, 1).Formula = "=Date(DasJahr,ersterMonat+ROW(A9)-9,1)"

Open in new window

Massimo ScolaAuthor Commented:
Excellent! That works.
Thanks Rgonzo
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.