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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,560
Messages
6,143,206
Members
450,469
Latest member
brent3162

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