Confused between Date and Today

[Joinersfolly]

In my code I used the bolded line below which seemed to work fine, however I found to my horror that each day I opened my workbook
all the dates changed to reflect whatever the date is today not what date 'Today' was the day i entered the code ie.

if on the 16/04/2016 i used "=Today()-" & Varname
16/04/2016-3 I got 13/04/2016 which is what i wanted.
However when i opened the sheet again on 20/04/2016 the cell result had changed to 20/04/2016-3 =17/04/2016

Once the date has been entered I do not want it to change again.

Will the change from the underlined code to the row below it work?

Regards

Dim LastRow As Long'Dim Varname As Integer
        
 Dim Unchanging As Variant
   Unchanging = Date
   'MsgBox Unchanging
 Dim Varname As Variant
        
        




        If Target.Address = "$D$1" Then
        Varname = Range("D1")


        
       
        
  'Range("A" & LastRow).Formula = "=Today()-" & Varname

        Range("A" & LastRow).Value = Unchanging - Varname

[Zack Barresse]

There's nothing you can do with the TODAY() function to make it return anything but the system date on recalculation, and since it's a volatile function it will update at any calc chain trigger anywhere.

To get around this, set the Value property instead of using the Formula property. You can either use the VBA.Date function, or use the Evaluate method to return the TODAY() equivalent. So it would look something like this...

[vba]Range("A" & LastRow).Value = VBA.Date - Varname[/vba]

To specifically answer your question, this will simplify your underlined code (which doesn't actually show as underlined when you wrap it in CODE tags).

[Joinersfolly]

Thanks for your help Zack.