Home > Back-end >  Summing Calculated Variable in Loop (VBA)
Summing Calculated Variable in Loop (VBA)

Time:03-09

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

CodePudding user response:

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

CodePudding user response:

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

  • Related