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

masinyaka 3 Posts Tuesday September 9, 2014Registration date September 25, 2014 Last seen - Sep 23, 2014 at 07:25 AM - Latest reply: masinyaka 3 Posts Tuesday September 9, 2014Registration date September 25, 2014 Last seen
- Sep 25, 2014 at 01:06 PM
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
See more 

4 replies

TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Sep 23, 2014 at 11:38 AM
-1
Thank you
Hi Masinyaka,

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

Best regards,
Trowa
masinyaka 3 Posts Tuesday September 9, 2014Registration date September 25, 2014 Last seen - Sep 24, 2014 at 08:12 AM
Hi TrowaD,
Thanks for the suggestion. I have done what you suggest, however the problem still persist.
Any additional help?
Regards
Masinyaka
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Sep 25, 2014 at 11:00 AM
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
masinyaka 3 Posts Tuesday September 9, 2014Registration date September 25, 2014 Last seen - Sep 25, 2014 at 01:06 PM
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