Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Autofill today's date Everyday
Some one tried to help me with this, but did not worked, so sorry for
reposting My Original Message I would like to see today's date in column A everyday in new cell, maintaining previous cell with yesteray's date and previous before previous cell with day before yesterday's date and so on. So every weekday you see today's date in the cell. e.g 08/20/2007 08/21/2007 08/22/2007 Today Will show up tomorrow as 8/23/2007 Will show up Friday as 8/24/2007 Will be one blank row for saturday & Sunday Will show up Monday as 8/27/2007 Thanks AM The answer was Dim mLastRow As Integer Dim mdate As Date mdate = Date If Weekday(mdate) 1 Then mLastRow = Range("A65000").End(xlUp).Row + 2 Else mLastRow = Range("A65000").End(xlUp).Row + 1 Range("A" & mLastRow).Value = mdate End If This will automatically check for saturday and sunday and will skip a row while inseting new date. I have tested this. Hope this helps!! But for some reason ot does not work for me, Any further help would be greatly appriciated. Thanks AM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Autofill today's date Everyday
You could do this with a macro, but how you implement it would depend on
when you want to put the date in - Every time the file is opened? - Each time the sheet is displayed? - When the sheet calculates? - When a button is pushed? James "AM" wrote in message ... Some one tried to help me with this, but did not worked, so sorry for reposting My Original Message I would like to see today's date in column A everyday in new cell, maintaining previous cell with yesteray's date and previous before previous cell with day before yesterday's date and so on. So every weekday you see today's date in the cell. e.g 08/20/2007 08/21/2007 08/22/2007 Today Will show up tomorrow as 8/23/2007 Will show up Friday as 8/24/2007 Will be one blank row for saturday & Sunday Will show up Monday as 8/27/2007 Thanks AM The answer was Dim mLastRow As Integer Dim mdate As Date mdate = Date If Weekday(mdate) 1 Then mLastRow = Range("A65000").End(xlUp).Row + 2 Else mLastRow = Range("A65000").End(xlUp).Row + 1 Range("A" & mLastRow).Value = mdate End If This will automatically check for saturday and sunday and will skip a row while inseting new date. I have tested this. Hope this helps!! But for some reason ot does not work for me, Any further help would be greatly appriciated. Thanks AM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Autofill today's date Everyday
I want to do it when any value is entered in col B
Thanks "Zone" wrote: You could do this with a macro, but how you implement it would depend on when you want to put the date in - Every time the file is opened? - Each time the sheet is displayed? - When the sheet calculates? - When a button is pushed? James "AM" wrote in message ... Some one tried to help me with this, but did not worked, so sorry for reposting My Original Message I would like to see today's date in column A everyday in new cell, maintaining previous cell with yesteray's date and previous before previous cell with day before yesterday's date and so on. So every weekday you see today's date in the cell. e.g 08/20/2007 08/21/2007 08/22/2007 Today Will show up tomorrow as 8/23/2007 Will show up Friday as 8/24/2007 Will be one blank row for saturday & Sunday Will show up Monday as 8/27/2007 Thanks AM The answer was Dim mLastRow As Integer Dim mdate As Date mdate = Date If Weekday(mdate) 1 Then mLastRow = Range("A65000").End(xlUp).Row + 2 Else mLastRow = Range("A65000").End(xlUp).Row + 1 Range("A" & mLastRow).Value = mdate End If This will automatically check for saturday and sunday and will skip a row while inseting new date. I have tested this. Hope this helps!! But for some reason ot does not work for me, Any further help would be greatly appriciated. Thanks AM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Autofill today's date Everyday
AM,
1. It doesn't matter which row in which the user is typing in column B? 2.What if the last cell in column A already contains the current date? James "AM" wrote in message ... I want to do it when any value is entered in col B Thanks "Zone" wrote: You could do this with a macro, but how you implement it would depend on when you want to put the date in - Every time the file is opened? - Each time the sheet is displayed? - When the sheet calculates? - When a button is pushed? James "AM" wrote in message ... Some one tried to help me with this, but did not worked, so sorry for reposting My Original Message I would like to see today's date in column A everyday in new cell, maintaining previous cell with yesteray's date and previous before previous cell with day before yesterday's date and so on. So every weekday you see today's date in the cell. e.g 08/20/2007 08/21/2007 08/22/2007 Today Will show up tomorrow as 8/23/2007 Will show up Friday as 8/24/2007 Will be one blank row for saturday & Sunday Will show up Monday as 8/27/2007 Thanks AM The answer was Dim mLastRow As Integer Dim mdate As Date mdate = Date If Weekday(mdate) 1 Then mLastRow = Range("A65000").End(xlUp).Row + 2 Else mLastRow = Range("A65000").End(xlUp).Row + 1 Range("A" & mLastRow).Value = mdate End If This will automatically check for saturday and sunday and will skip a row while inseting new date. I have tested this. Hope this helps!! But for some reason ot does not work for me, Any further help would be greatly appriciated. Thanks AM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Autofill today's date Everyday
If I understand your question correctly, I think this VBA code will work for
you... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then With Me.Cells(Rows.Count, 1).End(xlUp) If .Value < Date Then .Offset(1, 0).Value = Date End With End If End Sub It will place today's date in the next blank row of column A whenever a change is made (anywhere) in column B provided the last filled row in column A does not contain today's date already (this prevents multiple entries of today's date for multiple changes in column B within the same day). Rick "AM" wrote in message ... I want to do it when any value is entered in col B Thanks "Zone" wrote: You could do this with a macro, but how you implement it would depend on when you want to put the date in - Every time the file is opened? - Each time the sheet is displayed? - When the sheet calculates? - When a button is pushed? James "AM" wrote in message ... Some one tried to help me with this, but did not worked, so sorry for reposting My Original Message I would like to see today's date in column A everyday in new cell, maintaining previous cell with yesteray's date and previous before previous cell with day before yesterday's date and so on. So every weekday you see today's date in the cell. e.g 08/20/2007 08/21/2007 08/22/2007 Today Will show up tomorrow as 8/23/2007 Will show up Friday as 8/24/2007 Will be one blank row for saturday & Sunday Will show up Monday as 8/27/2007 Thanks AM The answer was Dim mLastRow As Integer Dim mdate As Date mdate = Date If Weekday(mdate) 1 Then mLastRow = Range("A65000").End(xlUp).Row + 2 Else mLastRow = Range("A65000").End(xlUp).Row + 1 Range("A" & mLastRow).Value = mdate End If This will automatically check for saturday and sunday and will skip a row while inseting new date. I have tested this. Hope this helps!! But for some reason ot does not work for me, Any further help would be greatly appriciated. Thanks AM |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Autofill today's date Everyday
There will be only one entry per day and I want to use this macro or
something else and lock the cell down. This os for weekday only. This macro can also be triggered at opening of file. Thanks A "Zone" wrote: AM, 1. It doesn't matter which row in which the user is typing in column B? 2.What if the last cell in column A already contains the current date? James "AM" wrote in message ... I want to do it when any value is entered in col B Thanks "Zone" wrote: You could do this with a macro, but how you implement it would depend on when you want to put the date in - Every time the file is opened? - Each time the sheet is displayed? - When the sheet calculates? - When a button is pushed? James "AM" wrote in message ... Some one tried to help me with this, but did not worked, so sorry for reposting My Original Message I would like to see today's date in column A everyday in new cell, maintaining previous cell with yesteray's date and previous before previous cell with day before yesterday's date and so on. So every weekday you see today's date in the cell. e.g 08/20/2007 08/21/2007 08/22/2007 Today Will show up tomorrow as 8/23/2007 Will show up Friday as 8/24/2007 Will be one blank row for saturday & Sunday Will show up Monday as 8/27/2007 Thanks AM The answer was Dim mLastRow As Integer Dim mdate As Date mdate = Date If Weekday(mdate) 1 Then mLastRow = Range("A65000").End(xlUp).Row + 2 Else mLastRow = Range("A65000").End(xlUp).Row + 1 Range("A" & mLastRow).Value = mdate End If This will automatically check for saturday and sunday and will skip a row while inseting new date. I have tested this. Hope this helps!! But for some reason ot does not work for me, Any further help would be greatly appriciated. Thanks AM |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Autofill today's date Everyday
AM, see if this does what you want. Copy this code, right-click on the
sheet tab, click on View Code and paste the code in there. Presumes row 1 is a heading row. James Private Sub Worksheet_Change(ByVal Target As Range) Dim myDate As Date ActiveSheet.Unprotect Password:="abc" Cells.Locked = False Columns(1).Locked = True Select Case WorksheetFunction.Weekday(Now) Case 2 To 6: myDate = Now Case 1: myDate = Now + 1 Case 7: myDate = Now + 2 End Select With Target If .Column = 2 Then Cells(Rows.Count, 1).End(xlUp).Offset(1) = myDate End If End With ActiveSheet.Protect Password:="abc" End Sub "AM" wrote in message ... There will be only one entry per day and I want to use this macro or something else and lock the cell down. This os for weekday only. This macro can also be triggered at opening of file. Thanks A "Zone" wrote: AM, 1. It doesn't matter which row in which the user is typing in column B? 2.What if the last cell in column A already contains the current date? James "AM" wrote in message ... I want to do it when any value is entered in col B Thanks "Zone" wrote: You could do this with a macro, but how you implement it would depend on when you want to put the date in - Every time the file is opened? - Each time the sheet is displayed? - When the sheet calculates? - When a button is pushed? James "AM" wrote in message ... Some one tried to help me with this, but did not worked, so sorry for reposting My Original Message I would like to see today's date in column A everyday in new cell, maintaining previous cell with yesteray's date and previous before previous cell with day before yesterday's date and so on. So every weekday you see today's date in the cell. e.g 08/20/2007 08/21/2007 08/22/2007 Today Will show up tomorrow as 8/23/2007 Will show up Friday as 8/24/2007 Will be one blank row for saturday & Sunday Will show up Monday as 8/27/2007 Thanks AM The answer was Dim mLastRow As Integer Dim mdate As Date mdate = Date If Weekday(mdate) 1 Then mLastRow = Range("A65000").End(xlUp).Row + 2 Else mLastRow = Range("A65000").End(xlUp).Row + 1 Range("A" & mLastRow).Value = mdate End If This will automatically check for saturday and sunday and will skip a row while inseting new date. I have tested this. Hope this helps!! But for some reason ot does not work for me, Any further help would be greatly appriciated. Thanks AM |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Autofill today's date Everyday
Both the code works and will do what I need,
Thanks a lot to all of you for the help. This group is great. Best, AM "Rick Rothstein (MVP - VB)" wrote: If I understand your question correctly, I think this VBA code will work for you... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then With Me.Cells(Rows.Count, 1).End(xlUp) If .Value < Date Then .Offset(1, 0).Value = Date End With End If End Sub It will place today's date in the next blank row of column A whenever a change is made (anywhere) in column B provided the last filled row in column A does not contain today's date already (this prevents multiple entries of today's date for multiple changes in column B within the same day). Rick "AM" wrote in message ... I want to do it when any value is entered in col B Thanks "Zone" wrote: You could do this with a macro, but how you implement it would depend on when you want to put the date in - Every time the file is opened? - Each time the sheet is displayed? - When the sheet calculates? - When a button is pushed? James "AM" wrote in message ... Some one tried to help me with this, but did not worked, so sorry for reposting My Original Message I would like to see today's date in column A everyday in new cell, maintaining previous cell with yesteray's date and previous before previous cell with day before yesterday's date and so on. So every weekday you see today's date in the cell. e.g 08/20/2007 08/21/2007 08/22/2007 Today Will show up tomorrow as 8/23/2007 Will show up Friday as 8/24/2007 Will be one blank row for saturday & Sunday Will show up Monday as 8/27/2007 Thanks AM The answer was Dim mLastRow As Integer Dim mdate As Date mdate = Date If Weekday(mdate) 1 Then mLastRow = Range("A65000").End(xlUp).Row + 2 Else mLastRow = Range("A65000").End(xlUp).Row + 1 Range("A" & mLastRow).Value = mdate End If This will automatically check for saturday and sunday and will skip a row while inseting new date. I have tested this. Hope this helps!! But for some reason ot does not work for me, Any further help would be greatly appriciated. Thanks AM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to autofill cell with today's date on edit | Excel Discussion (Misc queries) | |||
How do a set the current date to change in cell everyday | Excel Discussion (Misc queries) | |||
How do a set the current date to change in cell everyday | Excel Discussion (Misc queries) | |||
Display Date that does not change everyday. | Excel Discussion (Misc queries) | |||
Autofill today's date Everyday | Excel Programming |