Auto date A1 when typing in A2
I would like to automatically put the date in cell A1 when I type in A2. But I don't want to use =now() becuase it auto updates. You can get the date by pressing crtl and semicolon without updating itself but I can't figue out how to put this in a formula.
=IF(A2<>"",NOW(),"") but instead I want to use crtl + ;
Thanks.
Last edited by seanjacob; 08-16-2010 at 09:31 AM.
Hi,
Place this code in the worksheet module
To do this,Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, ActiveSheet.Range("A2")) Is Nothing Then Range("A1").Value = Now End Sub
right click the tab of the appropriate sheet, select view code
paste the code into the VBA editor that pops up
close the vba editor.
Please disregard anything in the above post. It may well have been edited without my consent, as has been the case with several posts and threads recently.
Thanks for the reply!
Sorry I didn't explain myself well. I would like this to run down the whole column so if typed into cell b6, a6 would show the date.
Also using now() makes the date auto update which I would like it not to do.
Now modified to timestamp column A next to an entry in column B.
Does this work for you?Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Not Intersect(Target, ActiveSheet.Range("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now End Sub
Last edited by sweep; 08-16-2010 at 08:40 AM. Reason: to trap .count > 1
Please disregard anything in the above post. It may well have been edited without my consent, as has been the case with several posts and threads recently.
Yes that worked great thankyou. Do you recon their is not a simple formula way of doing this? The only problem is that now() updates itself if only i could swap now() with crtl+; .
How do you solve a thread?
I don't know of any way to do what you want without code, sorry!
To mark your thread solved do the following: - Go to the first post - Click edit - Click Advance - Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve - Click Save
Please disregard anything in the above post. It may well have been edited without my consent, as has been the case with several posts and threads recently.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks