Sum an R1C1 Range

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

 

[Mike H]

One way

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


Mike

 

[Dave Peterson]

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

 

[Cinque Terra]

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

 

[Cinque Terra]

I figured it out. If I select sheet 2 before

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

it works.

Whoo Hoo!

 

[Mike H]

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

 

[Cinque Terra]

Nicely written - thanks!

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