asked on Excel 2010: Date Cell Automation
Hello Experts!
I'm curious if any one has (or can point me to) a formula or some .vba that I can use that automate the following process: I'd like it to do an Alt+Enter in ONE cell that contains dates that are vertical of one another and then add the present date (mm/dd/yyyy) at the bottom. It can be int he form of a button, you click and it does this or as soon as you click into the cell e.g.
Column K
**************************
* 7/1/2015 *
* 7/2/2015 *
* 7/3/2015 *
* 7/4/2015 *
* 7/5/2015 *
* 7/28/2015 <--present date added * <-----ONE Cell, let's call this K2 as an example.
**************************
Any help is GREATLY appreciated! Thank you.
I'm curious if any one has (or can point me to) a formula or some .vba that I can use that automate the following process: I'd like it to do an Alt+Enter in ONE cell that contains dates that are vertical of one another and then add the present date (mm/dd/yyyy) at the bottom. It can be int he form of a button, you click and it does this or as soon as you click into the cell e.g.
Column K
**************************
* 7/1/2015 *
* 7/2/2015 *
* 7/3/2015 *
* 7/4/2015 *
* 7/5/2015 *
* 7/28/2015 <--present date added * <-----ONE Cell, let's call this K2 as an example.
**************************
Any help is GREATLY appreciated! Thank you.
Microsoft ExcelSpreadsheetsMicrosoft OfficeVBA
Last Comment
itsmevic
ASKER
I went into the code. I see Sheet 1 which is my Raw Data sheet where I want to place the code at. I right click on Sheet1 (Raw Data) and select Insert > Module. It inserts the Module. I then double-click on the module to go into it. I then right click and paste the code. I click Save. I then click the green arrow to run the macro, It pops open the macro dialog box and wants me to give it a name. I give it the name "test" it then adds "test" as a sub. e.g.
Private Sub Worksheet_SelectionChange(
If Target.Address = [k78].Address Then
Target.Value = Target.Value & vbCrLf & Date
End If
End Sub
__________________________
Sub test()
End Sub
It's been a while since I've messed with this stuff, obviously I'm doing something wrong...
Private Sub Worksheet_SelectionChange(
If Target.Address = [k78].Address Then
Target.Value = Target.Value & vbCrLf & Date
End If
End Sub
__________________________
Sub test()
End Sub
It's been a while since I've messed with this stuff, obviously I'm doing something wrong...
No, it goes into the sheet's module, not a general module as you have done. The easiest way to get to the worksheet code module is to right-click the tab in the main Excel window and select "View Code". Otherwise, while in the VBE, double click the worksheet in the project explorer.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Try this code and then set it to run on keyboard shortcut:
(pop it in a normal module)
(pop it in a normal module)
Sub addTodayToBottom()
Cells(ActiveCell.End(xlDown).Row + 1, ActiveCell.Column).Value = Date
End Sub
Or this one can be less buggy:
Sub addTodayToBottom()
Cells(Range(Cells(Rows.Count, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column)).End(xlUp).Row + 1, ActiveCell.Column).Value = Date
End Sub
Steve, you're missing the vital requirement. The date is appended to the value already in the selected cell.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
No... the way I read this:
I'd like it to do an Alt+Enter in ONE cell that contains dates that are vertical of one another and then add the present date (mm/dd/yyyy) at the bottom.
Is that the date should be appended to the bottom of the currently selected column.
Not the currently selected cell.
I'd like it to do an Alt+Enter in ONE cell that contains dates that are vertical of one another and then add the present date (mm/dd/yyyy) at the bottom.
Is that the date should be appended to the bottom of the currently selected column.
Not the currently selected cell.
in ONE cell
Note the bolded ONE and the diagram. Plus Alt+Enter inserts a carraige return in a cell.
AH, I stand corrected... I totally mis-understood.
I would tend not to use the 'on change event' as it may add too many dates to the cell:
I would tend not to use the 'on change event' as it may add too many dates to the cell:
Sub addTodayToCell()
ActiveCell.Value = Activecell.value & vbCrLf & format(Date,"mm/dd/yyyy")
End Sub
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Great feedback, thank you guys. I'm going to actually end up using both code sets.
Wayne, for your code, I need the macro to encompass each cell on down starting at K78, what would be the proper syntax so that each cell in column K is going to do what the code below says? I just threw in the k200 as an example. I'm sure you understand what I"m try to describe.
Private Sub Worksheet_SelectionChange(
If Target.Address = [k78; k200].Address Then
Target.Value = Target.Value & vbCrLf & Date
End If
End Sub
Wayne, for your code, I need the macro to encompass each cell on down starting at K78, what would be the proper syntax so that each cell in column K is going to do what the code below says? I just threw in the k200 as an example. I'm sure you understand what I"m try to describe.
Private Sub Worksheet_SelectionChange(
If Target.Address = [k78; k200].Address Then
Target.Value = Target.Value & vbCrLf & Date
End If
End Sub
ASKER
Steve - Thanks for your input, for your code, let's say I just wanted to do it 'on click' rather than having to use a short cut key? Is there an OnClick variable we could add to your code and it do the same function as you've programmed it to thus far? I'm in and out of these cells a lot, to be able to just click on it and bam! it do the alt+enter, add present date to the bottom of that cell would be priceless, at least in my case it would be.
SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER CERTIFIED SOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Fantastic! If I could give you both 1000 points I would! Unfortunately, we'll have to settle and are limited to a measly 250 points each. :(
I'm using both macros now as I like the subtle differences in each. Again, thanks guys!
I'm using both macros now as I like the subtle differences in each. Again, thanks guys!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Open in new window
This code will go in the applicable worksheets code module (right-click tab > View Code). It will append the current date to the value already present in cell A1 (modify to suit).