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
Image 1 Code written in the initially question. enter image description here
Image 2 Code above enter image description here
