Sum an R1C1 Range
 

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!

Sum an R1C1 Range
 

One way

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


Mike

"Cinque Terra" wrote:

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!

Sum an R1C1 Range
 

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 wrote:

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!

--

Dave Peterson

Sum an R1C1 Range
 

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


"Mike H" wrote:

One way

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


Mike

"Cinque Terra" wrote:

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!

Sum an R1C1 Range
 

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!

"Cinque Terra" wrote:

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


"Mike H" wrote:

One way

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


Mike

"Cinque Terra" wrote:

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!

Sum an R1C1 Range
 

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" wrote:

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!

"Cinque Terra" wrote:

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


"Mike H" wrote:

One way

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


Mike

"Cinque Terra" wrote:

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!

Sum an R1C1 Range
 

Nicely written - thanks!

"Dave Peterson" wrote:

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 wrote:

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!

--

Dave Peterson