Massimo Scola
asked on
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
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
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
CalculateDates-.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Rgonzo