Dealing with 'Today()' formula in Excel VBA code [Solved/Closed]

Report
Posts
3
Registration date
Tuesday September 9, 2014
Status
Member
Last seen
September 25, 2014
-
Posts
3
Registration date
Tuesday September 9, 2014
Status
Member
Last seen
September 25, 2014
-
Hi Everyone,

I have this VBA code in a Excel sheet (specifically in the Module) which does the following:
- finds the empty cell in range "B:B"
- enters and formats today's date in the cell immediately below the empty one
- enters the name of the person entering the data below the date.

Sub findbottom()
'
' getDate_Name Macro
' request and insert date and name of person who did entry.
'
' Keyboard Shortcut: Ctrl+Shift+G
'
ActiveSheet.Cells(Rows.Count, 2).End(xlUp) _
.Offset(2, 0).Select
Selection.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "[$-809]dd mmmm yyyy;@"

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
ActiveCell.Select

yourName = InputBox("What is your name?")
ActiveSheet.Cells(Columns.Count, 2).End(xlUp) _
.Offset(1, 0).Value = "Data entered by: " & yourName

End With
ActiveSheet.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0).Select
End Sub


My problem is that any time I run the code, it updates all the previous dates to the current one. How do I modify the code to only enter today's date without updating previous dates entered on the sheet?

Thanks in advance for you help.
Masinyaka

1 reply

Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
446
Hi Masinyaka,

Change the following line
Selection.FormulaR1C1 = "=TODAY()"
into:
Selection = Now

Best regards,
Trowa
Posts
3
Registration date
Tuesday September 9, 2014
Status
Member
Last seen
September 25, 2014

Hi TrowaD,
Thanks for the suggestion. I have done what you suggest, however the problem still persist.
Any additional help?
Regards
Masinyaka
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
446
Hi Masinyaka,

Then I'm not sure what the issue is?

You said that all previous date's changed to today's date.
That was because you used a formula, which will always show today's date.

With my adjustment you will no longer enter a formula but a value, which won't change.

If you still have the TODAY function on your sheet, then those date's will change, but the newly entered date's won't, right?

Could you clarify your issue?

Best regards,
Trowa
Posts
3
Registration date
Tuesday September 9, 2014
Status
Member
Last seen
September 25, 2014

Hi Trowa
My bad. I see where the problem is. I still entered your suggesting as a formula

i.e. Selection = "Now()" instead of

Selection = Now

After clearing the mix-up it works perfectly fine.

Thanks very much for your time. Am grateful

Masinyaka