summing selections

nova30

New Member
Joined
Sep 22, 2002
Messages
14
Hi,

Is there a way to sum a range of cell, without actually reffering to the cel refferences. I am currently writing a macro which formats a variable length worksheet. Consequently I am having to use words in cells to define my selection, rather than the cell refferences. I need to find a way of summing the values between a cell named "Household" and a cell named "Subtotal". Both values are stored in column C, and the number of rows between these field changes so I can not hard wire in the actual formula.

Can anyone help!!

Cheers

Nova30
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi nova30,

How about this:

<pre>
'sum between names
Sub sumrange()
Dim SumArea As Range, Answer As Long

Set SumArea = Sheets("Sheet2").Range("Household", "Subtotal")
Answer = Application.WorksheetFunction.Sum(SumArea)
MsgBox Answer

End Sub

</pre>

HTH
 

nova30

New Member
Joined
Sep 22, 2002
Messages
14
Hi Richie

Thanks for the help, but I dont think i made my self very clear as the calculation is a little bit more complicated than I originally suggested!! Here is the code that I have written:

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

All I really need to do is find a way of summing the range in the last couple of lines of code. This range selects all the required infomation and allows the table to vary in length.

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

Cheers,

Nova30
 

Forum statistics

Threads
1,144,061
Messages
5,722,287
Members
422,420
Latest member
losc

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
Top