How do I hardcode a column in a formula? Currently I have the following but
I'd like want to modify the sum formula so that it sums the cells above minus
cell=C2, because 1st row is Headers? Is there any way to hard code the 2nd
part so that it's always subtracting from C2 and no other cells? I'd like it
so that it's always Column = C and Row = 2 that it subtracts from because the
number of columns changes in between column C and the last column depending
on the sheet.
lCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
With Cells(Rows.Count, lCol).End(xlUp)
.Offset(1, 0).FormulaR1C1 = _
"=SUM(R[-" & .Row - 1 & "]C:R[-1]C)-R[-" & .Row - 1 & "]C[-8]"
I would like to change C[-8] to be always Column C and not 8 columns to the
left of the cell with the formula. Any assistance would be appreciated.

--
Message posted via http://www.officekb.com

AHizon,

With Cells(Rows.Count, lCol).End(xlUp)
.Offset(1, 0).Formula = _
"=SUM(" & Range(Cells(2, lCol), .Cells(1, 1)).Address & ") - C2"
End With

HTH,
Bernie
MS Excel MVP


"AHizon via OfficeKB.com" <u38169@uwe wrote in message
news:79bf3150b0b50@uwe...
How do I hardcode a column in a formula? Currently I have the following
but
I'd like want to modify the sum formula so that it sums the cells above
minus
cell=C2, because 1st row is Headers? Is there any way to hard code the
2nd
part so that it's always subtracting from C2 and no other cells? I'd like
it
so that it's always Column = C and Row = 2 that it subtracts from because
the
number of columns changes in between column C and the last column
depending
on the sheet.
lCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
With Cells(Rows.Count, lCol).End(xlUp)
.Offset(1, 0).FormulaR1C1 = _
"=SUM(R[-" & .Row - 1 & "]C:R[-1]C)-R[-" & .Row - 1 & "]C[-8]"
I would like to change C[-8] to be always Column C and not 8 columns to
the
left of the cell with the formula. Any assistance would be appreciated.

--
Message posted via http://www.officekb.com

That did the trick...Thanks so much!

Bernie Deitrick wrote:
AHizon,

With Cells(Rows.Count, lCol).End(xlUp)
.Offset(1, 0).Formula = _
"=SUM(" & Range(Cells(2, lCol), .Cells(1, 1)).Address & ") - C2"
End With

HTH,
Bernie
MS Excel MVP

How do I hardcode a column in a formula? Currently I have the following
but
[quoted text clipped - 18 lines]
the
left of the cell with the formula. Any assistance would be appreciated.

--
Message posted via http://www.officekb.com