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 ScolaInternshipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

Just use one formula

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

Open in new window


Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Massimo ScolaInternshipAuthor Commented:
Excellent! That works.
Thanks Rgonzo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.