worksheet_calculate specific cell

    I am trying to get my excel spreadsheet to count the number of times another cell changes as a result of a calculation on another worksheet in my spreadsheet. Make sense? So I have it so that it currently counts successfully but it is counting for any calculation made and I want the counter to go up only when a specific cell is calculated.


    Here is the formula I have so far:


    Code
    Private Sub Worksheet_Calculate()
With Me.Range("B2:B3")
If Me.Range("B2") Then
Me.Range("G2").Value = ((Me.Range("G2").Value) + 1)
ElseIf Me.Range("B3") Then
Me.Range("G3").Value = ((Me.Range("g3").Value) + 1)
Else
End If
End With
End Sub


    So I want the value of G2 to go up only when B2 changes as the result of calculation. And I want G3 to go up only when B3 changes as the result of a calculation. With the code as it is now G2 goes up when B2 AND B3 change. G3 won't go up by one at all. What is wrong?

    Re: worksheet_calculate specific cell


    A couple of things.
    1. The code does not refer to the subject of the With Block, so the With Block serves no purpose as it stands
    2. The first If Statement only tests if B2 contains something, and not if its value has changed, therefore the second If statement only comes into play if B2 is empty


    To be able to test for changes you will need to maintain a variable for each cell, that contains the cells previous value. Only update the variable after the value changes and you have updated the counter. In practice for a large range you will need to use an array, the size of which matches the size of the range of cells monitored.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login