Summing Calculated Variable in Loop (VBA)

Question

I want to increment the total by the value of sump. Right now the msgBox is to make sure that the variable are correct, and they are. But I cannot figure out how to add each sump together.

 For cc = 1 To 4 'calculate chosen sum for products 1 to 4
        Range("Chosen").Cells(cc, 1).Select
        For p = 1 To lastrow
            Range("Proportion").Cells(p, 1).Select
            sump = Range("Proportion").Cells(p, 1).Value * Range("NCustomers").Value * Range("Chosen").Cells(p, cc).Value 'WorksheetFunction.Sum(Range("Chosen").Rows(p))
            MsgBox sump, vbOKCancel
        Next p
   Next cc
   MsgBox Total

sump = sump + Range(....

BigBen
– BigBen

2022-03-08 22:38:01 +00:00
Commented Mar 8, 2022 at 22:38 — BigBen
– BigBen, Commented Mar 8, 2022 at 22:38

VBasic2008 · Accepted Answer · 2022-03-09 01:27:36Z

1

Subtotals in Nested Loops

For cc = 1 To 4 'calculate chosen sum for products 1 to 4
    sump = 0
    For p = 1 To lastrow
        sump = sump + Range("Proportion").Cells(p, 1).Value * Range("NCustomers").Value * Range("Chosen").Cells(p, cc).Value
    Next p
    MsgBox sump, vbOKCancel
    Total = Total + sump
Next cc
MsgBox Total

answered Mar 9, 2022 at 1:27

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

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

1 Comment

Arielle Saunders Over a year ago

You are ammaazziinnggggg!

Arielle Saunders · Accepted Answer · 2022-03-09 05:06:18Z

0

So I got it to work,but now I am having issues pasting the total value. The loop is going through each cell in nc and pasting the value 4 times instead of pasting each total. I've commented the 2 lines that are the issue.

    For cc = 1 To 4 'calculate chosen sum for products 1 to 4
        Range("Chosen").Cells(cc, 1).Select
        For p = 1 To lastrow
            Range("Proportion").Cells(p, 1).Select
            sump = sump + Range("Proportion").Cells(p, 1).Value * Range("NCustomers").Value * Range("Chosen").Cells(p, cc).Value 'WorksheetFunction.Sum(Range("Chosen").Rows(p))
        Next p
            For nc = 1 To 4 'paste value in number chosen cell
                'Range("NumberChosen").Cells(1, nc).Select
                'ActiveCell.Value = sump
            Next nc
    Next cc
   MsgBox Total

End Sub

answered Mar 9, 2022 at 5:06

Arielle Saunders

514 bronze badges

3 Comments

VBasic2008 Over a year ago

Without For nc = ..., just after Next p, in my answer, use Range("NumberChosen").Cells(1, cc).Value = sump.

Arielle Saunders Over a year ago

Thank you! I am sooo excited at how this is coming along.

Ike Over a year ago

This is an extension of your original question - not an answer. Maybe you should edit your question ...???

Collectives™ on Stack Overflow

Summing Calculated Variable in Loop (VBA)

2 Answers 2

Subtotals in Nested Loops

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Subtotals in Nested Loops

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related