Sum of a specific range that changes on each iteration of a loop
sum using for loop - matlab
for loop sum
sum within a loop matlab
matlab iteration loop
matlab for loop sum matrix
matlab for loop accumulate
summation in matlab using while loop
I have a sheet that the values of a range change each time I change a specific cell. Let's say that the cell C8 is an indentity of a person and column H the scheduled monthly repayments. I need to find the aggregate monthly repayments, hence on each possible value of C8 (and that actually means for every person as you can think of different values of C8) I need the aggegate of repayments, hence the aggegate of cell Hi Hence, keeping row i constant and changing cell C8, I always need to sum Hi. So I actually need sum(Hi) (i constant and the index of the sum is cell c8, so if c8 takes value from 1 to 200, I need the sum(Hi(c8)), again row i . Hi(c8) it is just a notation to show you that Hi depends on the value of c8. The actual formula in cell H10 is INDEX('Sheet2'!R:R,MATCH('Sheet1'!$C$8,'Sheet2'!F:F,0)))). H11 and onwards have the same formula with slight twists for the fact that the repayments are not always equal, but the index function remains the same.
Then, the total of H10 for all possible values of c8 is pasted in c17, the total of H11 is pasted in C18 etc. Please find some images below, maybe that helps to support what I try to achieve. enter image description here
I have the following code for that purpose. Note that the above example was just to explain you a bit the background, the cells and the range that changes are different.
sub sumloop() Application.ScreenUpdating = False Application.DisplayStatusBar = False Sheets("Sheet1").Range("C8").Value = 1 Dim i, k As Integer i = 1 k = Sheets("Sheet1").Range("C9").Value Dim LR As Long LR = Sheets("Sheet1").Range("C" & Sheets("Sheet1").Rows.Count).End(xlUp).row Sheets("Sheet1").Range("C17:C" & LR).ClearContents Do While i <= k If (Sheets("Sheet1").Range("J9").Value = "") Then Sheets("Sheet1").Range("h10:h200").Copy Sheets("Sheet1").Range("c17").PasteSpecial Paste:=xlValues, Operation:=xlAdd, SkipBlanks:= _ False, Transpose:=False Else Sheets("Sheet1").Range("h9:h200").Copy Sheets("Sheet1").Range("c17").PasteSpecial Paste:=xlValues, Operation:=xlAdd, SkipBlanks:= _ False, Transpose:=False End If Sheets("Sheet1").Range("C8").Value = Sheets("Sheet1").Range("C8").Value+1 i = i + 1 Loop Sheets("Sheet1").Range("C8").Value = 1 Application.ScreenUpdating = True Application.DisplayStatusBar = True End Sub
The if inside of the loop is needed as the location of the first value of the range depends on some criteria which have not to do with the code. Also k denotes the maximum number of possible values. What I need is approximately 250.
While the code works, it takes approximately 40 seconds to run for 84 values of cell C8 and approximately 1.5 minute for 250. I tried some things, changed do while to for but nothing significant, used variable ranges instead of fixed ones like h10:h100, very similar to what I do with Sheet1.Range(C17:C&LR). Again no significant changes. As I am very new to vba I don't know if 1.5 minutes are a lot for such a simple code, but to me it seems a lot and this analysis is needed for 10 different combinations of 250 different values for cell c8, which means 15 minutes approximately.
I would appreciate if anyone can suggest me something faster.
Thank you very much in advance.
Here is a complete solution, with explainations in comments. Because we do not have you source spreadsheet, I could not run any tests on this.
Option Explicit 'This forces you to declare all your varaibles correctly. It may seem annoying at first glance, but will quickly save you time in the future. Sub sumloop() Application.ScreenUpdating = False 'Application.DisplayStatusBar = False -> This is not noticely slowing down your code as soon as you do not refresh the StatusBar value for more than say 5-10 times per second. 'Save the existing Calculation Mode to restore it at the end of the Macro Dim xlPreviousCalcMode As XlCalculation xlPreviousCalcMode = Application.Calculation Application.Calculation = xlCalculationManual 'Conveniently store the Sheet into a variable. You might want to do the same with your cells, for example: MyCellWhichCounts = MySheet.Range("c17") Dim MySheet As Worksheet MySheet = ActiveWorkbook.Sheets("Sheet1") MySheet.Range("C8").Value2 = 1 'It is recommended to use.Value2 instead of .Value (notably in case your data type is Currency, but it is good practice to use that one all the time) Dim LR As Long LR = MySheet.Range("C" & MySheet.Rows.Count).End(xlUp).Row 'Be carefull with "MySheet.Rows.Count", it may go beyond your data range, for example if you modify the formatting of a cell below your "last" row. MySheet.Range("C17:C" & LR).Value2 = vbNullString 'It is recommended to use vbNullString instead of ""; although I agree it makes it more difficult to read. Dim i As Integer, k As Integer 'Integers are ok, just make sure you neer exceed 255 k = MySheet.Range("C9").Value2 For i = 1 To k 'Use a For whenever you can, it is easier to maintain (i.e. avoid errors and also for you to remember when you go back to it years later) 'Little extra so you can track progress of your calcs Dim z As Integer z = 10 'This can have any value > 0. If the value is low, you will refresh your app often but it will slow down. If the value is high, it won't affect performance but your app might freeze and/or you will not have your Statusbar updated as often as you might like. As a rule of thumb, I aim to refresh around 5 times per seconds, which is enough for the end user not to notice anything. If i Mod z = 0 Then 'Each time i is a mutliple of z Application.StatusBar = "Calculating i = " & i & " of " & k 'We refresh the Statusbar DoEvents 'We prevent the Excel App to freeze and throw messages like: The application is not responding. End If 'Set the range Dim MyResultRange As Range If (MySheet.Range("J9").Value2 = vbNullString) Then MyResultRange = MySheet.Range("h10:h200") Else MyResultRange = MySheet.Range("h9:h200") End If '# Extract Result Data MyResultRange.Calculate 'Refresh the Range values Dim MyResultData As Variant MyResultData = MyResultRange.Value2 'Store the values in VBA all at once '# Extract Original Data Dim MyOriginalRange as Range MyOriginalRange.Calculate MyOriginalRange = MySheet.Range("c17").Resize(MyResultRange.Rows.Count,MyResultRange.Columns.Count) 'This produces a Range of the same size as MyResultRange Dim MyOriginalData as Variant MyOriginalData = MyOriginalRange.Value2 '# Sum Both Data Arrays Dim MySumData() as Variant Redim MySumData(lbound(MyResultRange,1) to ubound(MyResultRange,1),lbound(MyResultRange,2) to ubound(MyResultRange,2)) Dim j as long For j = lbound(MySumData,1) to ubound(MySumData,1) MySumData(j,1)= MyResultData(j,1) + MyOriginalData(j,1) Next j 'Instead of the "For j = a to b", you could use this, but might be slower: MySumData = Application.WorksheetFunction.MMult(Array(1, 1), Array(MyResultData, MyOriginalData)) MySheet.Range("C8").Value2 = MySheet.Range("C8").Value2 + 1 Next i MySheet.Range("C8").Value2 = 1 Application.ScreenUpdating = True Application.StatusBar = False 'Give back the status bar control to the Excel App Application.Calculation = xlPreviousCalcMode 'Do not forget to restore the Calculation Mode to its previous state End Sub
Added by OP (see comments)
Image 1 Code written in the initially question. enter image description here
Image 2 Code above enter image description here
Summation with looping - MATLAB Answers, for n=0:16 is the correct syntax, you are also making another error by doing the summation in every iteration, you should instead find all values and do the� Sub RoundToZero2() For Each c In Worksheets("Sheet1").Range("A1:D10").Cells If Abs(c.Value) < 0.01 Then c.Value = 0 Next End Sub If you do not know the boundaries of the range you want to loop through, you can use the CurrentRegion property to return the range that surrounds the active cell. For example, the following procedure, when run from a
OK, A few things.
Dim i, k As Integer doesn't do what you think it does, you need to do:
Dim i As Integer, k As Integer
Secondly don't use Integer in VBA use Long so
Dim i As Long, k As Long
Third the calculations are killing you. Turn them off with
Application.Calculation = xlCalculationManual at the start of your code and back on with
Application.Calculation = xlCalculationAutomatic at the end of your code.
Now we are presented with really fast code but the problem that it doesn't update on each iteration which you need it to do. You can calculate just a range like so:
Sheets("Sheet1").Range("h10:h200").Calculate so put that in just before you copy the range
There will be an even faster way to do this but I just can't seem to wrap my head around your requirements so I am unable to assist further.
Summation column matrix using for loop, For my homework I have a 4x5 matrix and I'm supposed to use the for statement to find the sums of each of the columns. I've figured it out using sum, but I don't� Let’s say that we want to loop through a for loop 10 times. We can use the range(1, 11) function inside a for statement to do that: for i in range(1, 11): print ('This is the', i, 'iteration.') When run, the code above gives the following output: >>> This is the 1 iteration. This is the 2 iteration. This is the 3 iteration. This is the 4
Welcome to StackOverflow. I must admit I got a bit confused by your narrative, as I did not fully understand if you are doing a sum(a,b,c) or a sum(sum(a,b,c), sum(d,e,f), ...). In any cases, a trick that will dramatically accelerate your script is the use of arrays.
Performing calcs with VBA is not slow, but retrieving the data from Excel (communicating with the application) IS slow, and pretty much depending on the number of "requests", rather than the quantity of data requested.
You can use arrays to request the data from a range all at once, isntead of requesting the value of each cell separately.
Dim Arr() As Variant Arr = Range("A1:E999")
It is as simple as this. Give it a try and if you are still struggling let us know.
If you are new to Arrays, keep in mind you can have a two-dimmensionnal array:
Dim 2DArray(0 to 10, 0 to 50)
Or a stacked array (an array of arrays):
Dim MyArray() as String Dim StackedArray() as MyArray Dim StackedArray() as Variant
You will need a 2D-Array for extracting the data from a range, but I feel you may need an Array of 2D-Arrays for your Sum of Sums.
Some recommended reading: https://excelmacromastery.com/excel-vba-array/
7. Iteration — How to Think Like a Computer Scientist: Learning with , The third line changes the value of a but does not change the value of b, Each item in turn is (re-)assigned to the loop variable, and the body of the loop is def sum_to(n): """ Return the sum of 1+2+3 n """ ss = 0 for v in range(n+1): ss The “computational rule” for creating the sequence is to start from some given n, and� The condition for this loop is n != 1, so the loop will continue until n is 1, which will make the condition false. At each iteration, the program prints the value of n and then checks whether it is even or odd. If it is even, the value of n is divided by two. If it is odd, the value is replaced by 3n+1.
How to achieve the same through pivot charts (no VBA)
First, you must organize your data in a specific way, where each column is a field, and each row is a data entry. If you are not familiar with databases, this is the most tricky point as you may arrange your data in different ways.
Long story short, we will take an example where you have 3 customers and 4 dates. So that is 12 data entries, which will provide the repayment value for each of the possible customer ID and date.
Select that data and insert a PivotChart. Note: you could insert a PivotTable alone, or a PivotChart alone. I recommend the option hwere you insert both, as managing your data will be more intuitive when working on the Chart. The table is updated at the same time you update the chart.
Make sure the all your data is selected, including the top row which will dictate the name of each field (the name of each column).
A new sheet has just been create, and you can see where both your PivotTble and PivotCharts will appear. Select the chart.
A menu to the right will appear (it might have already been there, so make sure you selected the Chart and not the Table, as that menu would be slightly different).
Drag and drop the field names into the categories as shown. What you are doing here is telling Excel what data you want to see (Values) and how you want to break it down (per date, and per customer).
By default dates data is always groupped quartile and year. To be able to see all the date we have data for, you can click the [+] near the data on the Table: this will show more details for both the table and the chart.
But we want to get completely rid of the quartils and years. In order to achieve this, you need to right click any value of your date column in the Table, and choose "Ungroup" as displayed.
Your data now looks like this. Note the time axis is not on scale. For example if you hae monthly data and a month is missing, there will be no gap. This is one of the difficulties with Pivot data. This can be overcomes, but it is off topic here.
Now we want to have a cumulative view of the data, so we want to play with the way the values are proessed by Excel. Select the chart, then in the right panel: right click on the "Sum of Repayment" field, and select "Value Field Settings".
In the "Show Values As" tab, select "Show values as" "Running Tital In". Then choose "Date". Here we are telling Excel that the value to display should be a cumulative total, cumulated according to the "Date" field. Press OK.
You now have what you are looking for. If you look in the Table, you have one column per Customer ID, and one row per date. For a given Date, you have the cumulative repayment made by a given Customer ID. At the very right, you have the Grand Total, which is, for a given date, the sum of all the Customer ID values.
The Chart keeps showing the cumulative payment per CUstomer ID, and we cannot see the grand total. In orer to achieve this, simply remove the "Customer ID" field from the "Legend (Series)" category area in the Fields Panel, as shown. (you can untick the Customer Id [x] box, or you can drag and drop it from the category area to the main list area).
Now we only have the Grand total in the chart. But why? If you display the "Value Field Settings" of Sum of Repyament" (Step 10), the first tab "Summarize Values By" will tell Excel what to do when several value meet the same Legend and Axis values. Now that we removed the Customer ID field from the Legend area, for each date, we have 3 repayment values (one for each Customer ID). In the field settings, we tell Excel to use a "Sum". So it returns the sum of the 3 values.
But you could play around and return the Average, or even use "Count", which will show you how many records you have (it will return 3). That is why pivot charts are so powerful: with only a few clicks and/or drag and drop, you can display a myriad of different graphics for your data.
For future interest, you should look online for Filters, and "Insert Slicer" (which is equivalent to filtering, but will add button directly on your chart: great when showing the data to colleagues and switch from one setting to another)
Hope this helped!
[PDF] 1 Loop Examples, Let's say we wanted to sum all 1, 2, and 3 digit prime numbers. In other words, given the vector [1 4 3], we want to end up with [1 1 4 4 3 3]. Finally, before each iteration of our while loop ends, we must increment our loop variable. used whenever you want to continue looping until some condition changes from true to� Loop control variable This is a sum of numbers that accumulates with each iteration of a loop.
for loops and the range function, For example, suppose we tried to change the value of each element in an array a third array which has the total of the prices and the taxes for each of the items are solved by iterating over a sequence of integers created by the range function, a list in memory containing all the elements specified through its arguments. For loop with range. In the previous lessons we dealt with sequential programs and conditions. Often the program needs to repeat some block several times. That's where the loops come in handy. There are for and while loop operators in Python, in this lesson we cover for. for loop iterates over any sequence.
7.6. The Accumulator Pattern — Foundations of Python Programming, updating the accumulator variable on each iteration (i.e., when processing each item For example, consider the following code, which computes the sum of the Inside the for loop, the update occurs. w has the value of current item (1 the first The range function takes at least one input - which should be an integer - and� First, you will create a loop that prints out the values in a sequence from 1 to 10. Then, you will modify that loop to also sum the values from 1 to 10, where at each iteration the next value in the sequence is added to the running sum.
Loop through a list of data on a worksheet by using macros, For more information about this change, read this blog post. will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NumRows = Range("A2", Range("A2"). (This code assumes that each cell in column A contains an entry until the end.). In Python, the variable in the for clause is referred to as the _____ because it is target of an assignment at the beginning of each loop iteration: Target variable Which of the following represents an example to calculate the sum of the numbers (accumulator)
- The reason it is slow is because it is calculating on each iteration of the loop (which you need) and there are probably multiple formulas in your sheet. Can you update your question with a clear goal of what you are trying to achieve and some sample data?. It looks like you are trying to compound some sums?
- I updated my question with some data and maybe a more clear explanation of what I need? Let me know if that helps.
- You could replace the formulas in your Excel sheet by a formula directly inside VBA. You could also quit VBA and run everything on your sheet, say by having one column per combination (first row would be your input, and the next rows would be your outputs).
- Ama thanks for your suggestion but in that case I will have a very large sheet with many columns. I am still trying how to optimise the code with arrays. I got a better run time however not as significant as I expected so I am still working on it. Thanks!
- Just sent you a full reponse. Let me know if that helps.
- First of allo huge thanks for your effort on that. While it is very helpful, it adds up all the values of column H and extract them in C17. What I really need is on each iteration of the loop to add seperately all the values of cell H10 (which changes on each iteration) contains. Then the total of every value of H10s will be pasted in cell C17, at the same time the aggregate of H11s (which again changes on each iteration) will be pasted in C18 etc. I tried to modified your code, however the only thing that comes to my mind is somehow to same in a two dimensional array all the combinations ...
- and so in the first row will be all the payments of person 1, then in the second column all the payments of person 2 etc. And then after the loop somehow to take the sum of the first row of this array (because the first row will be the payments of different persons on the same date) and paste it to c17, then the sum of the second row of the array and paste it in c18, and so on.
- This code does exactly what the code you provided in your question does. It has been optimized and refactored a little bit. So, is your question only about efficiency, or are you also struggling to get the Macro do what you would like it to do? If you need help beyond code optimization, then I highly recommend you submit an example of your worksheet (even if with fake values).
- Hi Ama, regarding my code my problem is its efficiency. I have attached you the run of my code and yours. I didn't change anything from yours. I didn't know how to attach them in my comment that's why I edited your answer. Sorry about this
- Sorry but I am so confused. Please send a screenshot showing the column and row coordinates. Change/remove the data if sensitive, but we need to see a full example. Also please make sure the code you posted in your question is the one you are using when running your tests, because I am 99% sure my code does the same as yours. (except maybe if some recalculations are not done; you can verify this be deleting the
Application.Calculation = xlCalculationManualline of my code)
- Thanks for your suggestions. I have tried to switch the calculations to Manual but as you said I need them inside of the loop. Also I need calculation to be active as column H takes its values from another sheet. Hence, whenever I change cell c8 data in column H changes as well, hence the other problem I had from deactivating the calculations was that column H was remaining unchanged. Maybe I should enable the calculation only on that sheet. Also, apologies for my narrative, I tried to update my questions and maybe now is more comprehensible.
- Thank you very much for your answer. Apologies for the confusion caused by my descreption. I need to sum all the different values for b1. Then in another cell all the different values for b2 etc. If we had two possible values for cell b1=1 and b1=2 and two values for cell b2=1 and b2=3 then I need sum(b1)=1+2=3 and in another cell sum(b2)=1+3=4. I hope this makes it more clear? Regarding your answer, I will try using arrays and see if this makes the code faster. Thanks again!
- Ama, I think the main problem with using arrays in my case is that I have to change the array inside of the loop as column H changes. Therefore, I think that the number of requests remain the same. As on each iteration of the loop array should be equal to the same range which have been updated with new data. I can't give values to the array before the loop as on that case the array will remain constant while the column H will keep changing on each iteration.
- Thank you so much once againfor your time and effort you put into my question! This answer is very helpful for organised data as you mentioned so it's good to know regardless my question. Again thanks!