Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a date that will not change
If a cell returns a certain value, can a date be made to appear in an
adjacent cell that will not change even if the first cell's value does change with time. For example, if cell A1=1, then B1 shows the date A1=1, even though A1 will eventually excede the value of 1 (therefore A1=1). I need a date macro that will show only the first date A1=0. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a date that will not change
To do what you want requires a Worksheet_Change event macro. The following
macro will put the date in B1 whenever A1 changes to anything =0. However, if B1 is already occupied, nothing will change in B1 no matter what happens in A1. Note that this macro must be placed in the sheet module of your sheet. To access that module, right-click on the sheet tab, select View code, paste this macro into that module. "X" out of the module to return to your sheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Target.Offset(, 1).Value) Then _ If Target.Value = 0 Then Target.Offset(, 1).Value = Date End If End Sub wrote in message ups.com... If a cell returns a certain value, can a date be made to appear in an adjacent cell that will not change even if the first cell's value does change with time. For example, if cell A1=1, then B1 shows the date A1=1, even though A1 will eventually excede the value of 1 (therefore A1=1). I need a date macro that will show only the first date A1=0. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a date that will not change
Thank you so very much. This is great.
Michael Otto Moehrbach wrote: To do what you want requires a Worksheet_Change event macro. The following macro will put the date in B1 whenever A1 changes to anything =0. However, if B1 is already occupied, nothing will change in B1 no matter what happens in A1. Note that this macro must be placed in the sheet module of your sheet. To access that module, right-click on the sheet tab, select View code, paste this macro into that module. "X" out of the module to return to your sheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Target.Offset(, 1).Value) Then _ If Target.Value = 0 Then Target.Offset(, 1).Value = Date End If End Sub wrote in message ups.com... If a cell returns a certain value, can a date be made to appear in an adjacent cell that will not change even if the first cell's value does change with time. For example, if cell A1=1, then B1 shows the date A1=1, even though A1 will eventually excede the value of 1 (therefore A1=1). I need a date macro that will show only the first date A1=0. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
How do I have the date in a spreadsheet change automically. | Excel Discussion (Misc queries) | |||
How do I have the date in a spreadsheet change automically. | Excel Discussion (Misc queries) | |||
Can I change a date with no format (20051111) to date format? | New Users to Excel | |||
date auto change | Excel Discussion (Misc queries) |