none
Counting rows until sum value reached RRS feed

  • Question

  • Hello,
    I am not able to make such simple thing work.

    Sheet contains values in column N and rows 10-500, I need to add these values until sum is equal or higher than some arbitrary value.

    When equal value or first higher value is reached, I need to count the number of rows used to reach the value.

    Actually these rows represent months and arbitrary value is to be raches for invesment to return. There are few guides how to do this but it does not work for me. Tried to modify but without success.  Thank you.

    Pete

    sfs

    Monday, October 14, 2019 11:24 PM

All replies

  • Hi fandango71,

    I don't know how we do it without Macro (or with using functions).
    So, I use VBA/Macro.
         when Sum(N8) >= Limit(M8), show the last row number(of column N) in O8   
         
    Private Sub Worksheet_Change(ByVal Target As Range)
        ' --- check if Range("N10:N500") changed
        If Not (Intersect(Target, Range("N10:N500")) Is Nothing) Then
            ' --- if sum[N8] greater/equal limit[M8], show the last row
            If (Range("N8").Value >= Range("M8").Value) Then
                ' -- column N = 14
                Range("O8").Value = Cells(Rows.Count, 14).End(xlUp).Row
            Else
                Range("O8").Value = ""
            End If
        End If
    End Sub
    Regards,

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Tuesday, October 15, 2019 10:16 PM correct URL
    • Proposed as answer by Ashidacchi Friday, October 18, 2019 10:32 AM
    Tuesday, October 15, 2019 1:28 AM
  • Hi, 

    Just checking in to see if the information of Ashidacchi was helpful. Please let us know if you would like further assistance.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Tuesday, October 15, 2019 8:08 AM
    Moderator
  • Hello,
    thank you much. But I am not very proficient at macros. Anyways I tried and when I try to run macro in developer context it always creates new Sub "name" and when I run it, it tells me:

    Compile error: expected End Sub:

    Sub fsfdsf()
    Private Sub Worksheet_Change(ByVal Target As Range)
        ' --- check if Range("N10:N500") changed
        If Not (Intersect(Target, Range("N10:N500")) Is Nothing) Then
            ' --- if sum[N8] greater/equal limit[M8], show the last row
            If (Range("N8").Value >= Range("M8").Value) Then
                ' -- column N = 14
                Range("O8").Value = Cells(Rows.Count, 14).End(xlUp).Row
            Else
                Range("O8").Value = ""
            End If
        End If
    End Sub
    End Sub


    sfs

    Tuesday, October 15, 2019 12:31 PM
  • Hi fandango71,

    Your code  --- Sub in Sub. This causes Syntax Error.
    Sub fsfdsf()
    Private Sub Worksheet_Change(ByVal Target As Range)
    ....
    End Sub
    End Sub
    Remove "Sub fsfdsf()" and the last "End Sub" like this.
    ' --- Sub fsfdsf() ' -- to be deleted
    Private Sub Worksheet_Change(ByVal Target As Range)
    ....
    End Sub
    ' --- End Sub ' -- to be deleted

    Regards,

    Ashidacchi -- http://hokusosha.com

    Tuesday, October 15, 2019 10:23 PM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    No VBA, but PQ's M code.
    http://www.mediafire.com/file/2rxzhx11eypld3v/10_16_19a.xlsx/file
    http://www.mediafire.com/file/akwckjjnb5mrkea/10_16_19a.pdf/file

    Thursday, October 17, 2019 2:47 AM
  • Hello,
    when I trz to run just the provided code, it seems it does not detect it as macro since it openes a dialog where I can create new macro and the only button I can click is "Create", after this it creates automatically new codelines:
    

    sfs

    Saturday, October 19, 2019 3:41 PM
  • Hi fandango71,

    You need to place the macro in the "Sheet" you want to count sum, instead of "Module".


    Regards,

    Ashidacchi -- http://hokusosha.com



    • Edited by Ashidacchi Saturday, October 19, 2019 10:44 PM
    Saturday, October 19, 2019 10:36 PM
  • Hello,
    thank you for your effort. I have the state as it is on attached picture. When I save it and click run I get the diaalog for creating macro. And it automatically creates module.

    

    sfs

    Sunday, October 20, 2019 7:34 PM
  • Hi fandango71,

    I suppose  "Module" is not created automatically.
    So, I cannot understand your situation and what "module" means.
    And what is the "dialog", what does it say?
    I'm afraid you need to describe your situation/issue more detailed and clearly.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Sunday, October 20, 2019 9:45 PM
  • Hello Ashidacchi,
    here is what I am doing step by step:

    1) I open desired XLSM - macro enabled XLS
    2) Go to desired sheet
    3) Go to Developer -> Macros
    4) I have to enter "macro name" - so lets say its macro1 and I can select "Macros in" where I select only the desired XLSM (so not "This workbook" or "All..")
    5) The only button I can select is "Create" - so I do
    6) Microsoft Visual Basic for Applications opens, automatically focused on "Module1" which contains only following:
    Sub macro1()

    End Sub

    7) We dont want module so I click on desired sheet in "Microsoft Excel Objects" flder instead of "Module1" in "Modules" folder

    8) I replace 
    Sub macro1()

    End Sub

    with your code.

    9) I click "Save"
    10) I click "Run" and following window opens

    sfs

    Monday, October 21, 2019 8:39 AM
  • Hi fandango71,

    I'm afraid you need to know the meaning or behavior of "Worksheet_Change(ByVal Target As Range)" and function "Intersect". Please search them via web.

    If you write the code I provided in Sheet2 (by VB Editor), it will work with Sheet2 automatically. i.e. when you change value in Range("N10:N500"), macro will work automatically.

    I hope you will learn VBA step by step. I recommend to read some books about Excel VBA.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Monday, October 21, 2019 8:58 AM
  • Hi fandango71,

    I cannot explain more, but hope my sample will teach something for you.
    I've shared "Sum_Limit.xlsm" via OneDrive. Please download and try it.
    Regards,

    Ashidacchi -- http://hokusosha.com

    Monday, October 21, 2019 10:00 AM
  • Hi Ashidacchi,
    I will dig a bit more deeper. Thank you very much!


    sfs

    Monday, October 21, 2019 1:38 PM
  • Hi sfs,

    Any updates about this problem now?

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, October 23, 2019 5:07 AM
    Moderator
  • Hi Emi,
    I have to reserve a time for this task. Hope after this weekend will have update.

    Regards,
    Bohdan

    sfs

    Wednesday, October 23, 2019 10:59 AM