# Counting rows until sum value reached

• ### 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 Tuesday, October 15, 2019 10:16 PM correct URL
• Proposed as answer by 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.

Tuesday, October 15, 2019 8:08 AM
• 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 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

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.
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,

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.