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

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Sep 23, 2014 at 11:38 AM
Hi Masinyaka,

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

Best regards,
Trowa
-1
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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
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