Sum an R1C1 Range

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

Mike H

One way

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


Mike
 
D

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
 
C

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
 
C

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

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
 
C

Cinque Terra

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top