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!
|