Mixing Absolute and Relative Reference in a Formula

Hello,
I am trying to mix absolute and relative references in a formula. I am
trying to sum a column who's length changes. One time there may be 20 cells
in the column with data and the next time it might have 50.
I know that the data starts in the same place every time, cell A15. I use a
Do Until loop to find the last cell with data in the column. After I find
the last cell in the the column, I move to the next cell down. My total will
go in this cell. I then want to sum the column, starting in cell A15 to
whatever cell is the last one in the column.
I think I need something like SUM(A15,ActiveCell.Offset(-1,0)).....Is
something like that possible?

Dim rng as Range
set rng = cells(rows.count,1).End(xlup).Offset(1,0)
rng.FormulaR1C1 = "=Sum(R15C:R[-1]C)"

or

Range("A15").End(xldown).Offset(1,0).formulaR1C1 = _
"=Sum(R15C:R[-1]C)"

--
Regards,
Tom Ogilvy




"tedd13" wrote:

> Hello,
> I am trying to mix absolute and relative references in a formula. I am
> trying to sum a column who's length changes. One time there may be 20 cells
> in the column with data and the next time it might have 50.
> I know that the data starts in the same place every time, cell A15. I use a
> Do Until loop to find the last cell with data in the column. After I find
> the last cell in the the column, I move to the next cell down. My total will
> go in this cell. I then want to sum the column, starting in cell A15 to
> whatever cell is the last one in the column.
> I think I need something like SUM(A15,ActiveCell.Offset(-1,0)).....Is
> something like that possible?
>
>

If there are no populated cells below your data:

With Range("A" & Rows.Count).End(xlUp)
.Formula = "=SUM(A15:A" & .Row - 1 & ")"
End With

In article <[email protected]>,
tedd13 <[email protected]> wrote:

> Hello,
> I am trying to mix absolute and relative references in a formula. I am
> trying to sum a column who's length changes. One time there may be 20 cells
> in the column with data and the next time it might have 50.
> I know that the data starts in the same place every time, cell A15. I use a
> Do Until loop to find the last cell with data in the column. After I find
> the last cell in the the column, I move to the next cell down. My total will
> go in this cell. I then want to sum the column, starting in cell A15 to
> whatever cell is the last one in the column.
> I think I need something like SUM(A15,ActiveCell.Offset(-1,0)).....Is
> something like that possible?

No need to lopp through the cells.

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
Cells(iLastRow+1,"A").Formula = "=SUM(A15:A" & iLastRow & ")"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"tedd13" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> I am trying to mix absolute and relative references in a formula. I am
> trying to sum a column who's length changes. One time there may be 20
cells
> in the column with data and the next time it might have 50.
> I know that the data starts in the same place every time, cell A15. I use
a
> Do Until loop to find the last cell with data in the column. After I find
> the last cell in the the column, I move to the next cell down. My total
will
> go in this cell. I then want to sum the column, starting in cell A15 to
> whatever cell is the last one in the column.
> I think I need something like SUM(A15,ActiveCell.Offset(-1,0)).....Is
> something like that possible?
>
>

wouldn't it be

With Range("A" & Rows.Count).End(xlUp)(2)
.Formula = "=SUM(A15:A" & .Row - 1 & ")"
End With


--
Regards,
Tom Ogilvy


"JE McGimpsey" wrote:

> If there are no populated cells below your data:
>
> With Range("A" & Rows.Count).End(xlUp)
> .Formula = "=SUM(A15:A" & .Row - 1 & ")"
> End With
>
> In article <[email protected]>,
> tedd13 <[email protected]> wrote:
>
> > Hello,
> > I am trying to mix absolute and relative references in a formula. I am
> > trying to sum a column who's length changes. One time there may be 20 cells
> > in the column with data and the next time it might have 50.
> > I know that the data starts in the same place every time, cell A15. I use a
> > Do Until loop to find the last cell with data in the column. After I find
> > the last cell in the the column, I move to the next cell down. My total will
> > go in this cell. I then want to sum the column, starting in cell A15 to
> > whatever cell is the last one in the column.
> > I think I need something like SUM(A15,ActiveCell.Offset(-1,0)).....Is
> > something like that possible?
>

Yup - misread...thanks for the correction.

In article <[email protected]>,
Tom Ogilvy <[email protected]> wrote:

> wouldn't it be
>
> With Range("A" & Rows.Count).End(xlUp)(2)
> .Formula = "=SUM(A15:A" & .Row - 1 & ")"
> End With
>