How to add the current date to a cell.?

[Moradyn]

If A1 is blank, and A2=blank and a3= Now()

When i enter the name 'Steve' into A1, i want cell A2 to enter the current date (and stay that way forever)


Anyone know how ?

[Yogi Anand]

You can enter the current date in any cell with CTRL+; -- this date will stay the same; however if you put =NOW() in a cell the date will be dynamic!
HTH

[dk]

If A1 is blank, and A2=blank and a3= Now()

When i enter the name 'Steve' into A1, i want cell A2 to enter the current date (and stay that way forever)


Anyone know how ?

Hi,

There's no formula which can put the current date/time in a cell which won't update when the worksheet is recalculated (unless someone can prove me wrong). Therefore, you could use a simple bit of VBA code. Right click the worksheet tab and choose View Code. Then paste this:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value = "Steve" Then
Me.Range("A2").Value = Now
End If
End Sub

The procedure Worksheet_Change is fired in Excel every time you change a cell on the worksheet. All this code does it say 'if the changed cell (Target) is cell A1 and the value of Target is Steve then put the current date/time into A2'.

HTH,
Dan

[Moradyn]

Thanks Dan, i will try this out asap...


Can you tell me what i would need to change there to make it work for a whole column, and a date..

ie: if i enter something into A2:A5000, it would add the date i entered it into B2:B5000. (respectively)

Regards

Steve

[dk]

How about this?

Let me know if it suits your needs.

Dan

Private Sub Worksheet_Change(ByVal Target As Range)
'Only run macro if a single cell was changed
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub

If Target.Row < 5001 And Target.Column = 1 And Target.Value = "Steve" Then
    Application.EnableEvents = False
    Me.Range("B" & Target.Row).Value = Now
    Application.EnableEvents = True
End If
End Sub

[Moradyn]

I entered that, and it works, but only for Steve, is there something that i can change to make it when something is typed in A1 say, any sort of text, like *.*

<----- vba n00bie

(as you have guessed!)

Many thanks

Steve

[Moradyn]

ahh figured it out, just had to change it to

<>" "

heh, Many thanks Dan!


Steve

[Ivan F Moala]

On 2002-04-13 14:39, Moradyn wrote:
I entered that, and it works, but only for Steve, is there something that i can change to make it when something is typed in A1 say, any sort of text, like *.*

<----- vba n00bie

(as you have guessed!)

Many thanks

Steve

Amended

Private Sub Worksheet_Change(ByVal Target As Range)

'Only run macro if a single cell was changed

If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub



If Target.Row < 5001 And Target.Column = 1  Then

    Application.EnableEvents = False

    Me.Range("B" & Target.Row).Value = Now

    Application.EnableEvents = True

End If

End Sub

[Chris Davison]

On 2002-04-13 14:14, dk wrote:
There's no formula which can put the current date/time in a cell which won't update when the worksheet is recalculated (unless someone can prove me wrong).

Dan, is it you or Mudface who has a tenner riding on this ?

[dk]

Chris,

I think I might have mentioned a little wager a while ago. Why, do you have a cunning plan?

Dan