Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date functions
First entered I guess means if the data changes leave the first date in.
Here is an example for data entered in column A. Dates are automatically entered in column B: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub End If If IsEmpty(Target.Offset(0, 1)) Then Application.EnableEvents = False Target.Offset(0, 1).Value = Now() Application.EnableEvents = True End If End Sub This is worksheet code and does not go in a standard module. -- Gary''s Student gsnu200712 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date functions
Now having had a chance to look at this a little closer, I am coming to the
conclusion it is not what I was hoping it might be. It's a macro and therefore not something I can run from within a cell (as I understand the help documentation). I was looking for a custom function and I know next to nothing about VBA, so am unsure how to make this into one, if it can be made into one at all. I've tried changing 'Sub' into 'Function' but it does do it for me. The good news is that you have given me a much better idea as to how to start. For instance, providing I don't try to use a range, the guts of the macro work very well in a cell as an IF() statement, e.g. IF (C2<"", NOW(), ""). I now just need to understand how to make it work with a range of target cell values. I've tried IF (C2:H2<"", NOW(), "") and also IF (C2<"" AND B2<"" AND... etc, NOW(), "")but this doesn't work - obvious to you, perhaps, why, but not yet to me... "Gary''s Student" wrote: This version will work with any cell, not just cells in column A This version works with mutiple cells, so copy/paste will record the date for an entire block of cells: Private Sub Worksheet_Change(ByVal Target As Range) For Each r In Target If IsEmpty(r.Offset(0, 1)) Then Application.EnableEvents = False r.Offset(0, 1).Value = Now() Application.EnableEvents = True End If Next End Sub -- Gary's Student gsnu200712 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date functions
This version will work with any cell, not just cells in column A
This version works with mutiple cells, so copy/paste will record the date for an entire block of cells: Private Sub Worksheet_Change(ByVal Target As Range) For Each r In Target If IsEmpty(r.Offset(0, 1)) Then Application.EnableEvents = False r.Offset(0, 1).Value = Now() Application.EnableEvents = True End If Next End Sub -- Gary's Student gsnu200712 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Functions | Excel Worksheet Functions | |||
IF and Date Functions | Excel Worksheet Functions | |||
Date Functions | Excel Discussion (Misc queries) | |||
Date functions | New Users to Excel | |||
Date functions | Excel Discussion (Misc queries) |