Running total loop within loop in vba

Question

I'm trying to make my loop at the bottom increase the value m by m each time. For example if m = .002, I want it to increase by .002 for each cell from week_start to 53. I tried to do that with my "decrease" variable.

Let me know if you know how to make a loop add on to itself with each iteration but just for my variable m.

Thanks!

Sub decrease_projections()
Dim pd As Worksheet
Dim p As Double
Dim week_start As Integer

Set pd = Worksheets("CSD_Proj_Data")

'clear contents for new values
pd.Range("R2:R53").ClearContents

'reset projected values
For i = 11 To 53 Step 1

pd.Cells(i, 18) = pd.Cells(i + 253, 4)

Next i

'get input
p = Val(InputBox("Enter percentage to decrease projections over time gradually.", "Decrease Projections", (0))) / 100
w = Val(InputBox("Enter week to begin decrease.", "Decrease Projections"))

w = Trim(w)

pd.Range("z1") = w

week_start = pd.Range("AA1")

'calculate percentage loss
m = p / 30

'for loop to calculate decreased sales projections
    
For i = week_start To 53 Step 1

    decrease = m * (53 - week_start)
    d = pd.Cells(i, 18) * (1 - decrease)
    pd.Cells(i, 18) = d
            
Next i

'pd.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Introduce another Dim loopCount As Long variable. Initialize it to loopCount = 1 before the loop starts, then decrease = m * (53 - week_start) * loopCount and loopCount = loopCount + 1. This should increase your decrease value with every iteration of the loop. — PeterT
– PeterT, Commented Mar 17, 2021 at 23:40
Does that mean to create a loop around the loop I have and just have it count? Then inside my current loop have it multiply by the count? Thanks — Alexa
– Alexa, Commented Mar 17, 2021 at 23:55

PeterT · Accepted Answer · 2021-03-18 00:12:54Z

1

I believe what you're asking about is increasing your decrease value with every loop. Here is an example of how you can create a variable to modify the value with each iteration.

'for loop to calculate decreased sales projections
Dim loopCount As Long
loopCount = 1
For i = week_start To 53 Step 1
    decrease = m * (53 - week_start) * loopCount
    d = pd.Cells(i, 18) * (1 - decrease)
    pd.Cells(i, 18) = d
    loopCount = loopCount + 1
Next i

answered Mar 18, 2021 at 0:12

PeterT

8,5971 gold badge19 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Running total loop within loop in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related