sum a range from a starting point that varies

[sevi61]

Chi,
hopefully someone can help with this;

I have a named cell (First_Cell) which changes each month ie A1,B1,C1. I
have a formula that sums First_Cell:Z1 I am trying to write a macro that will
take the column reference (A,B,C) that First_Cell is in, go down 1 row and
sum from that column to the end of the range (if First_Cell is in cell C1, I
need the macro to sum from C2:Z2).
any assistance provided would be gratefully accepted.

 

[Rick Rothstein \(MVP - VB\)]

Here is one way...

=SUM(OFFSET(INDIRECT(First_Cell&":Z1"),1,0))

Rick

 

[FSt1]

hi
if First_Cell changes each month and your formula is based on that then you
really don't need the column reference. First_Cell is both column and row
reference.
Sub fcell()
Dim v As Range
Set v = Range("First_Cell:Z1")
MsgBox WorksheetFunction.Sum(v)
End Sub

regards
FSt1