Sum variable ranges
 

Two part question:
I have a spreadsheet where I want to sum one of a few ranges.

E15 will contain the sum of E7:E14. Then E30 will contain the sum of
E17:E29 and so on. This is part of a loop function where the code loops
through the sheet and when it hits "Progam Total", it assignes the the
formula in question.

Here is the current code:

Sub Totals()
Dim Client As Range
Dim Spend As Range
Dim Sales As Range

Set Client = Sheet1.Range("C7")
Set Spend = Sheet1.Range("E7")
Set Sales = Sheet1.Range("F7")

Do Until ActiveCell = "STOP"
If Client = "Program Total" Then
Spend = FORMULA TO ADD THE VARIABLE RANGE
Sales = FORMULA TO ADD THE VARIABLE RANGE
Client = Client.Offset(1, 0).Select
Spend = Spend.Offset(1, 0).Select
Sales = Sales.Offset(1, 0).Select
Else
Client = Client.Offset(1, 0).Select
Spend = Spend.Offset(1, 0).Select
Sales = Sales.Offset(1, 0).Select
End If

End Sub

Second Question:
Is there a better way to do this? My experience is that there always is...

Thanks!

Sum variable ranges
 

I assume your loop is closed in your actual code. It is not in your posting.

I don't know that there is a better way, but there is a different way. You
could use a For ... Next loop. You could possibly use a case statement
instead of the If ...Then statement.

My philosophy is that if the code works it is good enough. But I do not
sell my services, so I can afford to be satisfied with that philosophy. A
professional would not be.

Your first question was not very clear about summing one of a few ranges.
You need to clarify that requirement, like which range you want to sum out of
which few.

"PJFry" wrote:

Two part question:
I have a spreadsheet where I want to sum one of a few ranges.

E15 will contain the sum of E7:E14. Then E30 will contain the sum of
E17:E29 and so on. This is part of a loop function where the code loops
through the sheet and when it hits "Progam Total", it assignes the the
formula in question.

Here is the current code:

Sub Totals()
Dim Client As Range
Dim Spend As Range
Dim Sales As Range

Set Client = Sheet1.Range("C7")
Set Spend = Sheet1.Range("E7")
Set Sales = Sheet1.Range("F7")

Do Until ActiveCell = "STOP"
If Client = "Program Total" Then
Spend = FORMULA TO ADD THE VARIABLE RANGE
Sales = FORMULA TO ADD THE VARIABLE RANGE
Client = Client.Offset(1, 0).Select
Spend = Spend.Offset(1, 0).Select
Sales = Sales.Offset(1, 0).Select
Else
Client = Client.Offset(1, 0).Select
Spend = Spend.Offset(1, 0).Select
Sales = Sales.Offset(1, 0).Select
End If

End Sub

Second Question:
Is there a better way to do this? My experience is that there always is...

Thanks!