+ Reply to Thread
Results 1 to 10 of 10

Counting a variable whose value is derived from other live values constantly changing.

  1. #1
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Counting a variable whose value is derived from other live values constantly changing.

    I have a live input of numbers (from a live constantly changing source) say in cell A1 . Every time the number in A1 changes( it may be + or - but it will be different from the previous number), I want to add 1 in the cell B1( and keep adding in B1 as the live input keeps coming and changing in A1 ). can some one help?
    Someone nice helped me with this formula….


    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("A1").Address Then
    Range("B1").Value = Range("B1").Value + 1
    End If
    End Sub

    The above formula is great but it works only if a data is directly inputted in the cell A1. If A1 = c1-d1 , then the values don’t change in B1 and in my case the live values are coming on cell C1 and D1 from where live difference is constantly updated in A1.
    Please help me with a solution. Thanx !

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Counting a variable whose value is derived from other live values constantly changing.

    Use this macro instead:

    Please Login or Register  to view this content.
    Gary's Student

  3. #3
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Counting a variable whose value is derived from other live values constantly changing.

    Tons of thanks .. you are a wizard !

  4. #4
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Counting a variable whose value is derived from other live values constantly changing.

    What if you needed to introduce similar values in a1 & b2, and a2&b2 (in the same sheet ?

  5. #5
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Counting a variable whose value is derived from other live values constantly changing.

    The code can easily be expanded.
    We would need a list of cell pairs [formulaCell, counterCell]

  6. #6
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Counting a variable whose value is derived from other live values constantly changing.

    This code is for two pairs [A1,B1] and [A2,B2]:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Counting a variable whose value is derived from other live values constantly changing.

    Thanx a ton ! God bless you.... I would like to keep in touch with you for what this means to me is just beyond anybodys imagination.
    Thanks again

  8. #8
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Counting a variable whose value is derived from other live values constantly changing.

    Thank you for the feedback!

  9. #9
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Counting a variable whose value is derived from other live values constantly changing.

    Suppose in cell C1 is the price and D1 is the volume of a certain scrip.
    Every time the price goes up( ie C1>OLDC1), in cell B1 we add 1. Concurrently in the cell A1 we add the Volume( D1-Prev D1)
    Every time the price goes down( ie C1<OLDValueC1), in cell B1 we add -1. Concurrently in the cell A1 we add the Volume( D1-Prev D1) in a negative value=-(D1-OLDValueD1)
    How can we write this for 2 pairs ?
    It will be so kind of you to help me out.

  10. #10
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Counting a variable whose value is derived from other live values constantly changing.

    Many Thanks to Jakobshavn who solved this big problem with supremacy !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1