Keeping count of cell changes

[heartofblueice]

I'm trying to find a way where excel will keep a running count of how many times a cell has been changed in the next column. For example, if cell A5 is changed, B5 value would be 1. When A5 is changed again, B5 is 2, etc. I also need it to work for a range because it's a growing spreadshet. Is it possible to have this to work even if cell is modified thru the copy and paste functions?

Any help is appreciate!

 

[Jon von der Heyden]

Hello and welcome to the board.

Perhaps;
In the Sheet Module:

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Worksheet_Change([COLOR=blue]ByVal[/COLOR] Target [COLOR=blue]As[/COLOR] Range)
    [COLOR=blue]If[/COLOR] Target.Column = 1 [COLOR=blue]Then[/COLOR] [COLOR=green]'! i.e. if it is column A[/COLOR]
        [COLOR=blue]With[/COLOR] Target(1, 2)
            .Value = .Value + 1
        [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[dado6612]

Hi
this is very interesting thing
but i have an problem..
this isnt working if cell is autorefreshing (ex formula)

is there any way to make it work in situation like that?

 

[Jon von der Heyden]

Hi

What is the formula in column A? Whilst we could use a calculate event I think we would do better to track the changes of its precedents.

 

[dado6612]

It's actually H column, formula is a little bit long
H2=IF(IF(ISTEXT(D3);SUBSTITUTE(D3;".";",")+0;D3/1000)<1;IF(ISTEXT(D3);SUBSTITUTE(D3;".";",")+0;D3);IF(ISTEXT(D3);SUBSTITUTE(D3;".";",")+0;D3/1000))

Where D3 is value extracted from web and it's updating frequently
I tried with column D also and is same as for column H

Ofcourse, when I change them manually, then code is counting..

 

[dado6612]

zvrrrrrrrrrrrrrrrk
nothing?

 

[Jon von der Heyden]

Because it is a query I don't think it is triggering when the cell changes. I'm sure there must be a much better way thanthis but so far this is what I came up with...

In the ThisWorkbook module:

[COLOR="Blue"]Dim[/COLOR] varOldResults [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Workbook_Open()
    [COLOR="Blue"]With[/COLOR] Sheet1
        varOldResults = Application.Transpose(Intersect(.UsedRange, .Range("H2:H" & Rows.Count)))
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]
[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Workbook_SheetCalculate([COLOR="Blue"]ByVal[/COLOR] Sh [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR])
    [COLOR="Blue"]Dim[/COLOR] rngCell [COLOR="Blue"]As[/COLOR] Range
    Application.EnableEvents = [COLOR="Blue"]False[/COLOR]
    [COLOR="Blue"]If[/COLOR] Sh.CodeName = "Sheet1" [COLOR="Blue"]Then[/COLOR]
        [COLOR="Blue"]With[/COLOR] Sheet1
            [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] rngCell [COLOR="Blue"]In[/COLOR] Intersect(.UsedRange, .Range("H2:H" & Rows.Count))
                [COLOR="Blue"]If[/COLOR] rngCell.Value <> varOldResults(rngCell.Row - 1) [COLOR="Blue"]Then[/COLOR]
                    [COLOR="Blue"]With[/COLOR] rngCell.Offset(, 1)
                        .Value = .Value + 1
                    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR] rngCell
            varOldResults = Application.Transpose(Intersect(.UsedRange, .Range("H2:H" & Rows.Count)))
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    Application.EnableEvents = [COLOR="Blue"]True[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

This could have problems. The variable varOldResults will be wiped if you have any other code that runs into unhandled errors.

 

[Jon von der Heyden]

Hi

Can you please keep this in the public forum rather than PM me? That way others can contribute, and who knows even maybe something comes out of this that others can learn from.

When you say it hasn't worked can you tell me what you have tried and explain what the outcome was? I.e. any errors? spurious results? etc.

 

[dado6612]

Nothing happened
I copied your code into thisworkbook part, change sheet1 in my sheet name, saved, refreshed data..and no effect
I don't have any other code in that sheet, or even workbook

 

[Jon von der Heyden]

The code I posted assumes that you have formulas in Sheet1 (obviously name change required) column H. Can you confirm that your query affects the results of the formulas in column H? And can you confirm that the refresh would have caused changes in those formula results?