Counting the number of times a cell value changes

[Rajus]

I could use some help. I am trying to figure out a way to track the no. of times a cell value changes. The result column will be in the same spreadsheet. The counter should go up by one when the user makes the change and closes the file. This is just to ensure if the cell value changes multiple times while the workbook is still open, I would want to count it as one change.

Could someone help?

Thanks.

 

[Derek]

Hi Rajus

This might give you some ideas

Right click your sheet tab, left click View Code and paste this code in the white space:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then [A2].Value = 1
End Sub

Note: this assumes the cell you are checking is A1 and A2 is being used to record when changes are made to A1

Now doubleclick on ThisWorkBook in the VBA project window and paste this code into the white space:

Private Sub Workbook_Open()
[A2].Value = 0
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If [A2].Value = 1 Then [A3].Value = [A3].Value + 1
End Sub

Note: A3 holds your tally of changes to A1. The Open event resets A2 to zero and the Close event adds 1 to your score in A3 when you close the workbook if there has been any changes to A1

hope this helps
regards
Derek

 

[Colo]

Hi Rajus,

I think Workbook_BeforeClose event is suitable in your case.

Option Explicit
'Place this code in Thisworkbook Module
'Need a worksheet named "Preserve" to preserve a cell value.
'Assume:
'A tracked cell  is Sheets("Sheet1").Range("A1")
'The result cell is Sheets("Sheet1").Range("B1")

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Sheets("Sheet1").Range("A1").Value <> Sheets("Preserve").Range("A1").Value Then
        Sheets("Sheet1").Range("B1").Value = Sheets("Sheet1").Range("B1").Value + 1
        Sheets("Preserve").Range("A1").Value = Sheets("Sheet1").Range("A1").Value
    End If
End Sub

 

[Rajus]

First up, Thanks very much to you both for the quick response. I tried Derek's solution and it works great. Since I am not an expert on Excel, I am unable to expand the solution to a series of cells.

My spreadsheet looks like this:

Col A Col B
10/21/2003
10/22/2003
11/30/2003

The no. of entries in Col A will not be a fixed number. Will grow as the need arises. With the above scenario, I would need the corresponding cell in column B updated dynamically as changes are made to entries in column A.

Also, since this macro kicks off when a close is attempted, the counter would get updated even if I do not close the book and cancel the close operation. Is there something we could do to run this macro only when the user does close the book?

Thanks again so much for your help.

 

[Derek]

Hello again

Aha, I did not consider someone cancelling a close file call. In that case change my codes in ThisWorkBook to an open event only:

Private Sub Workbook_Open()
If [A2].Value = 1 Then [A3].Value = [A3].Value + 1
[A2].Value = 0
End Sub

working on the next bit
regards
Derek

 

[Derek]

Hello again

Different approach:

Paste this as the worksheet event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then Target.Offset(0, 1).Value = 1
End Sub


Paste this in ThisWorkBook:

Private Sub Workbook_Open()
Columns("B:B").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
, Transpose:=False
Application.CutCopyMode = False
Columns("B:B").ClearContents
End Sub

With luck this will use column B to note changes to a cell in column A and tally the changes in column C. You can hide column B if you want (may need slight amendment to code).

regards
Derek

 

[AmandaSS]

Counting the number of times a value changes within a row

Hi,

I am in need to find a way to calculate how many time numbers in a row changes.
To explain myself I give you an example.
I have a row of 6 cells. In each cell there is a figure (which represents a price) like following:


10 20 10 10 6 20

in this row I have six cells, each of them containing a price, but only 3 different prices (10 , 20 and 6).
What I need is to create a 7th column that gives me the number of times price changes in the row. In this case 3.

Thanks a lot. I hope I was able to explain the issue
Best
Amanda