Summing a range

nova30

New Member
Joined
Sep 22, 2002
Messages
14
I am working with the following code, and need to find a way of summing
the final range within the code, The code selects a range based on the words within the certain cells.Once I have the summed value I then need to paste it into another cell within the worksheet.

Public Sub SelectTabel()
Dim cStart As Range
Dim cEnd As Range
Dim cStartcal As Range
Dim cEndCal As Range

' first look for the first cell with *post store opening* in it
Set cStart = Range("A1")
' now check for the number cell
While Not LCase(Mid(cStart.Value, 1, 18)) = "post store opening"
' set the cEnd to the next cell
Set cStart = cStart.Offset(1, 0)
Wend
' set the end equal to the start range
Set cEnd = cStart
' Offset the cStart cell to the first cell in range for summing
Set cStartcal = cStart.Offset(2, 3)
' now check for the total cell
While Not LCase(cEnd.Value) = "total"
' set the cEnd to the next cell
Set cEnd = cEnd.Offset(1, 0)
Wend
' Offset the cStart cell to the last cell in range for summing
Set cEndCal = cEnd.Offset(0, 2)
' now select the range you need
' This is the selection that I need to sum
Range("C" & cStartcal.Row & ":" & "C" & cEndCal.Row).Select
End Sub


Is this possible? and how would I sum the selection?

Cheers,

Nova30
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi,

Introduce a variable and assign the sum of the range to it

<pre>Dim RngSum as Double

RngSum = WorksheetFunction.Sum(Range("C" & cStartcal.Row & ":" & "C" & cEndCal.Row))</pre>

Give this a try and let us know how it works out.
 
Upvote 0
Hi Jay!

I tried what you said but nothing happened! How do I assign the variable RngSum to a cell though?
 
Upvote 0
Hi,

Range("B1") = RngSum

would be one way to do it.

Also, try the following

Range("B1") = "=SUM(" & Range("C" & cStartcal.Row & ":C" & cEndCal.Row).Address & ")"

This should, if I haven't made a typo, put the range into an Excel formula.
 
Upvote 0

Forum statistics

Threads
1,226,456
Messages
6,191,145
Members
453,643
Latest member
adamb83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top