Auto Cumulate

[help needed yes please]

Hi, I havea column ‘A’ which is numerical. I <o></o>
I: want tohave a cell in another column which will auto increase by ‘1’ each time anentry is made in column ‘A’<o></o>
I Calculatecolumn ‘A’ in 30 day cycles. So:<o></o>
IE: A103should show number in designated cell (Example G50) ‘13’<o></o>
When entry placed in A104 shows in G50 ‘14’ I am lost here so any help be great. Thanks.Frank<o></o>

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Any time you enter any value in Column A the value in Range("G50") will increase by one.
This entry must be entered in column A manually and not as a result of a formula.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modifed 5/15/18 10:35 PM EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Range("G50").Value = Range("G50").Value + 1
End If
End Sub

 

[shknbk2]

Here is my answer to account for the 30-day cycle. (Same code installation as described above)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Range("G50").Value = Cells(Rows.Count, 1).End(xlUp).Row Mod 30
        If Range("G50").Value = 0 Then Range("G50").Value = 30
    End If
End Sub

 

[help needed yes please]

Hi, My only experience is in entering into a cell (Say G59) data like =Sum(blah:Blah) Could you walk me through how to enter the formula you so kindly worked out? I have seen formulas presented elsewhere here and feel so silly I don't know how to understand it. Thanks, frank

 

[help needed yes please]

Hi, please see my response to entry below. I am a bit lost, if you could walk me through the 'HOW' to enter your formula. Thanks frank.

 

[My Aswer Is This]

Not sure what your question is.
I gave you these instructions on how to install your script.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window​

 

[help needed yes please]

Thank you, I shall try as you suggest but that may not be for a day or so. In the meantime take care to enjoy life. Thanks F

 

[help needed yes please]

Hello, I shall try to rut more clarity into my query.( I have done a good job of mucking it up thus far)
A B C D E F<o></o>

17/05/2018<o></o>​	9am<o></o>	th<o></o>	4881<o></o>​	180<o></o>​
18/05/2018<o></o>​	8am<o></o>	f<o></o>	5081<o></o>​	200<o></o>​

<tbody> </tbody>

<o> </o>
F30<o></o>
The number shownin D1 is 4881 which is the 18^th day of a 30 day cycle (F30 show 12)<o></o>
D2 is 5018which is the 19^th day of a 30 day cycle.(F30 show 11)<o></o>
I am tryingto put a formula into Cell F30 which sequentially worksbackward.<o></o>
IE: As the numberof days into 30 day cycle increases <o></o>
Cell F30shows 1 day less as balance. (30 down to 1) Hope that helps you to help me as I have not been able to understand the advice so far. NOT YOUR FAULT , regards Frank<o></o>

 

[shknbk2]

What about my code updated based on your new information?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r as Integer
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        r = Cells(Rows.Count, 1).End(xlUp).Row
        Range("F30").Value = Cells(r, 4).Value Mod 30
        If Range("F30").Value = 0 Then Range("F30").Value = 30
    End If
End Sub

Although, mine shows that 4881 is day 21 (4860 is divisible by 30), and 5018 is day 8 (5010 is divisible by 30).

 

[shknbk2]

Never mind. I just re-read your explanation about counting down, not up. My code counts up.