Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of changes to a cell
Is there a function to keep track (count) of how many times a particular
cell, the date in the cell, has changed? Thanks!!! R |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of changes to a cell
It's possible and is fairly complicated but may not be practical in most
applications. http://www.oaltd.co.uk/Excel/Default.htm Look for Previous.zip -- Biff Microsoft Excel MVP "RMan" wrote in message ... Is there a function to keep track (count) of how many times a particular cell, the date in the cell, has changed? Thanks!!! R |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of changes to a cell
Event code can do this.
Enter a 0 in C1 then copy/paste this code into your worksheet module. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo enditall Application.EnableEvents = False If Target.Value < "" Then With Target.Offset(0, 2) .Value = .Value + 1 End With End If enditall: Application.EnableEvents = False End Sub Right-click on the sheet tab and "View Code" Paste into that sheet module. Whe you change A1 C1 will add to the count each time. Gord Dibben MS Excel MVP On Wed, 3 Oct 2007 13:00:00 -0700, RMan wrote: Is there a function to keep track (count) of how many times a particular cell, the date in the cell, has changed? Thanks!!! R |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of changes to a cell
Gord, I couldn't get this to work properly.
It only counted a change once. The counter cell went from 0 to 1 and that's it! You left events disabled at the end. Even turning events back on didn't make a difference. -- Biff Microsoft Excel MVP "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Event code can do this. Enter a 0 in C1 then copy/paste this code into your worksheet module. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo enditall Application.EnableEvents = False If Target.Value < "" Then With Target.Offset(0, 2) .Value = .Value + 1 End With End If enditall: Application.EnableEvents = False End Sub Right-click on the sheet tab and "View Code" Paste into that sheet module. Whe you change A1 C1 will add to the count each time. Gord Dibben MS Excel MVP On Wed, 3 Oct 2007 13:00:00 -0700, RMan wrote: Is there a function to keep track (count) of how many times a particular cell, the date in the cell, has changed? Thanks!!! R |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of changes to a cell
Works for me.
Enter 0 in C1 and add something to A1 Change A1 and number in C1 increases for me each time a change is made. Thanks for pointing out the not turning avents back on. Damn copy and paste<g Gord On Fri, 5 Oct 2007 02:45:25 -0400, "T. Valko" wrote: Gord, I couldn't get this to work properly. It only counted a change once. The counter cell went from 0 to 1 and that's it! You left events disabled at the end. Even turning events back on didn't make a difference. -- Biff Microsoft Excel MVP "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Event code can do this. Enter a 0 in C1 then copy/paste this code into your worksheet module. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo enditall Application.EnableEvents = False If Target.Value < "" Then With Target.Offset(0, 2) .Value = .Value + 1 End With End If enditall: Application.EnableEvents = False End Sub |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of changes to a cell
Well, I must have screwed up something when I first tried this!
It works now. Much better than the method I referenced. -- Biff Microsoft Excel MVP "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Works for me. Enter 0 in C1 and add something to A1 Change A1 and number in C1 increases for me each time a change is made. Thanks for pointing out the not turning avents back on. Damn copy and paste<g Gord On Fri, 5 Oct 2007 02:45:25 -0400, "T. Valko" wrote: Gord, I couldn't get this to work properly. It only counted a change once. The counter cell went from 0 to 1 and that's it! You left events disabled at the end. Even turning events back on didn't make a difference. -- Biff Microsoft Excel MVP "Gord Dibben" <gorddibbATshawDOTca wrote in message . .. Event code can do this. Enter a 0 in C1 then copy/paste this code into your worksheet module. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo enditall Application.EnableEvents = False If Target.Value < "" Then With Target.Offset(0, 2) .Value = .Value + 1 End With End If enditall: Application.EnableEvents = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of digits in a cell | Excel Worksheet Functions | |||
Count Number or words in Cell | Excel Discussion (Misc queries) | |||
count each cell that have a number and take that number and count. | Excel Discussion (Misc queries) | |||
Count number of values in a cell | Excel Worksheet Functions | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions |