• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Required: Excel Formula To Update Incremental Counter

J

James Perry

Member
Cell A3 has a changing numeric value.
Cell B2 has a fixed number e.g. 100
Cell B3 has the formula = INT(A3/B2)
E.g. = INT(550/100) gives 5 in cell B3
What i need.....
Excel formula in cell B1 (like a incremental counter) to do a summation of the values in cell B3 each time the value in cell A3 is changed.

In the above e.g B1 will give the value 5.
Next, the value in cell A3 is changed to 5550
So = INT(5550/100) gives the value 55
So the value n cell B1 should read 60 (5+55)
And so forth.
Thank u.
 
Hi James,

Try below code in sheet module:

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tempVar As Long

Application.ScreenUpdating = False
Application.EnableEvents = False

If Not Intersect(Target, Range("A3")) Is Nothing Then 'change A3 range to your choice
    tempVar = Range("B1").Value
    Range("B1") = tempVar + Range("B3")
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
 

End Sub

Regards,
 
You must log in or register to reply here.
Back
Top