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?

Thanks

Massimo

excelscreengrab.PNG
Sub AddRow()
    
    'Variables
    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
        .ListRows.Add
    End With
    Next
    

    
    '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
    '===========================================================================

    'suggestion:
    .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

CalculateDates-.xlsm
Massimo ScolaAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

Just use one formula

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

Open in new window


Regards
0
 
Massimo ScolaAuthor Commented:
Excellent! That works.
Thanks Rgonzo
0
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.