Solved

Simple counter down column

Posted on 2014-04-12
5
242 Views
Last Modified: 2014-04-13
Hi All,

I am looking for a simple counter that counts every time a cell changes in Column B.

Examples:

If B10 changes then I would like to have a "1" in G10.  If B10 changes again then a "2" is in G10 and so on.

Similarity, if B23 changes then I would like to have a "1" in G23.  If B23 then changes again 3 more times then G23 should equal 4.

...and so on for each relevant row down the column.

If the corresponding cell in "I" becomes a "1" then the counter should reset to zero and if there is "" in the same cell then the counter is "ready" to start counting again.

I have included a sample sheet to get going on this.

thanks.
counter.xlsm
0
Comment
Question by:BostonBob
  • 2
  • 2
5 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39997236
Not sure what yo meant by "If the corresponding cell in "I" becomes a "1" then the counter should reset to zero and if there is "" in the same cell then the counter is "ready" to start counting again. " so currently my code doesn't accommodate that.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
        Range("G" & Target.Row) = Range("G" & Target.Row).Value + 1
    End If
End Sub

Open in new window


Put the code in the worksheet module.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39997250
HI,

You could do it with this formula ( the first cell should have a zero no formula)

=IF(J11=1,0,IF(B11<>B10,H10+1,H10))

Open in new window


if you only want to see the change number once use conditional formatting like in the example

Regards
counterV1.xlsm
0
 

Author Comment

by:BostonBob
ID: 39997276
MacroShadow,

Thanks for that.  To answer your question column "I" + relative row should be used to reset the count back to zero.  If it can be reset by another way then please let me know.  This is all going into an automated program so I need some way to do that.

Rgonzo1971

Thanks for that.  Not sure if this does the job. The counter increases as we go from low rows to higher rows.  The counts for each row is independent of the other.

thanks!
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39997354
Ok. Thanks for the clarification.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
        Select Case Range("I" & Target.Row).Value
            Case Is = ""
                Range("G" & Target.Row) = Range("G" & Target.Row).Value + 1
            Case Is = "1"
                Range("G" & Target.Row) = ""
        End Select
    End If
End Sub

Open in new window

0
 

Author Comment

by:BostonBob
ID: 39997384
Beautiful!  Thanks!!!!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question