Help with vba formula to autopoulate

Copper Contributor

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

 

I am new to VBA.  I have a column of dates and I am running a simple formula to calculate the time since that date.  For some reason, the formula is not completing.  It is returning the results from the first cell down the sheet instead of the results for each cell.  What am I missing?

3 Replies
To make it stranger, the formula appears to have correctly pasted, but it did not execute. It just copied the results of the formula from the original cell instead of completing the formula.

@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.

@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.