Help with vba formula to autopoulate

Michael_Beach 

If I may add one more suggestion...

To fix this issue, you can use an absolute reference for the column and a relative reference for the row.

Here’s an example of how you can modify your code:

ActiveCell.FormulaR1C1 = "=DAYS(TODAY(),R[0]C[-1])"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

This should give you the desired result of calculating the number of days between today and each date in the column.

I hope this helps!

*All information as always without guarantee.

Michael_Beach 

It appears that you are trying to calculate the number of days between each date in a column and today's date. However, the issue you are facing is that the formula is not updating for each cell in the range.

The issue lies in the use of the autofill method, which is not necessary in this case.

Instead, you can directly assign the formula to the entire range at once. Like this:

Sub CalculateDaysSinceDate()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    ' Set reference to the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the name of your sheet
    
    ' Find the last row with data in column E
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
    ' Assign the formula to the entire range in one go
    ws.Range("E2:E" & lastRow).FormulaR1C1 = "=DAYS(TODAY(),RC[-1])"
End Sub

This will calculate the number of days between each date in column D (one cell to the left of column E) and today's date, and store the results in column E, starting from row 2 (assuming row 1 contains headers). The FormulaR1C1 property is used to specify the formula in R1C1 notation, which is a relative notation based on the row and column of the cell being updated.