Value Change Range of Sum in Another Cell

[Al Franz]

How would you program a number in a cell that would affect a SUM range in
another cell.

A1 = 2, then
A2 = Sum(B1...C1)

A1 = 5, then
A2 = SUM(B1...F1)

 

[Guest]

you can make it only using formula & function.
if programming it, you can do it as following:

Sub sumup()

Dim rngSum As Range
Dim rngB1 As Range
Dim a1 As Long

a1 = Sheet1.Range("A1").Value
Set rngB1 = Sheet1.Range("B1")
Set rngSum = Sheet1.Range(rngB1, rngB1.Offset(0, a1 - 1))

Debug.Print rngSum.Address
Debug.Print Application.WorksheetFunction.Sum(rngSum)

End Sub

 

[David G]

How would you program a number in a cell that would affect a SUM range in
another cell.

In A2, use the formula "=IF(A1 = 2,Sum(B1:C1), IF(A1 =
5,SUM(B1:F1),))"
This will make A2 = 0 if A1 is neither 2 nor 5.
If you use a macro, it will only be recalculated when the macro is
run. When you use a formula, the calculation is dynamic.

Cheers,
David