Sum an R1C1 Range

  • Thread starter Thread starter Cinque Terra
  • Start date Start date
C

Cinque Terra

I am attempting to sum the range selected by this code:
With Sheets("Sheet1")
.Range(.Cells(Row1, Col1), .Cells(Row2, Col2)).Select
End With

Thanks in advance for your help!
 
You don't need to select the range to get the sum:

Dim mySum as double

With Sheets("Sheet1")
mysum = application.sum(.Range(.Cells(Row1, Col1), .Cells(Row2, Col2)))
End With
msgbox mysum
 
Execellent - Thank you!

May I ask a follow-on question? I need to calculate a ratio between the
same range on two different sheets. In the code that follows, WriteSum
evaluates perfectly. But the code fails to select the range on the second
sheet. Any thoughts?

My code:
With Sheets("sheet1")
.Range(.Cells(Row1, Col1), .Cells(Row2, Col2)).Select
End With

For Each c In Selection
If (IsNumeric(c)) Then WriteSum = WriteSum + c.Value
Next

With Sheets("sheet2")
.Range(.Cells(Row1, Col1), .Cells(Row2, Col2)).Select
End With

For Each c In Selection
If (IsNumeric(c)) Then GridSum = GridSum + c.Value
Next

Ratio = (txt_SpreadAmt.Value - GridSum + WriteSum) / WriteSum
 
Yes that's trght but be careful you must also select sheet 1 even if it's
already selected or the code may fail on a second run

Mike
 
Nicely written - thanks!

Dave Peterson said:
You don't need to select the range to get the sum:

Dim mySum as double

With Sheets("Sheet1")
mysum = application.sum(.Range(.Cells(Row1, Col1), .Cells(Row2, Col2)))
End With
msgbox mysum
Click to expand...
 
Back
Top