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