LBee
New Member
- Joined
- Dec 25, 2021
- Messages
- 20
- Office Version
- 365
- 2019
- Platform
- Windows
I have this Macro, which look in column A where I have different dates, and writes in column B how many days has passed since the date in column A
The above code works fine, but I'm trying to replace TODAY with a fixed date I have in cell D2, and it looks easy, but I really can't figure it out
I have tried so many different things, but everything gives me an error, or a strange date instead of number of days.
Can someone give me a little push in the right direction?
VBA Code:
Sub Test()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>TODAY()+TIME(0,0,0),0,ROUNDUP(TODAY()-RC[-1],0))"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:" & "B" & Lastrow)
Range("B2:" & "B" & Lastrow).Select
Selection.NumberFormat = "@"
End Sub
The above code works fine, but I'm trying to replace TODAY with a fixed date I have in cell D2, and it looks easy, but I really can't figure it out

I have tried so many different things, but everything gives me an error, or a strange date instead of number of days.
Can someone give me a little push in the right direction?
Last edited by a moderator: