Dealing with 'Today()' formula in Excel VBA code

Solved/Closed
masinyaka Posts 3 Registration date Tuesday September 9, 2014 Status Member Last seen September 25, 2014 - Sep 23, 2014 at 07:25 AM
masinyaka Posts 3 Registration date Tuesday September 9, 2014 Status Member Last seen September 25, 2014 - 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
Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 23, 2014 at 11:38 AM
Hi Masinyaka,

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

Best regards,
Trowa
masinyaka Posts 3 Registration date Tuesday September 9, 2014 Status Member Last seen September 25, 2014
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
masinyaka Posts 3 Registration date Tuesday September 9, 2014 Status Member Last seen September 25, 2014
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
0