summing selections

[nova30]

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

 

[Richie(UK)]

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]

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