select last column and last nonblank cell

[AHizon via OfficeKB.com]

Hello,

I recorded a Macros & modified slightly to select the last column and last
nonblank cell in that column, but I would like to know how to find the last
nonblank column and last nonblank cell in that column to sum all the column
after the header. Below is the coding that I have:

Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-27]C:R[-1]C)-R[-27]C[-8]"

I want Column "K" to be dynamic so that it finds the last column instead of
hard coding to only Column K. I would also like the formula to be dynamic so
that it sums all the cells above the last nonblank cell minus cell(C2). Can
anyone assist me with this?

Thanks,
Abigail

 

[JW]

This will find the last col, the last used row in the column, and
place a formula in the cell below that one that sums all of the cells
above it (not counting the header row). That what you are after?
Sub thisHere()
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)"
End With
End Sub

Not really sure what you meant by

 

[AHizon via OfficeKB.com]

That worked...Thanks alot!!

This will find the last col, the last used row in the column, and
place a formula in the cell below that one that sums all of the cells
above it (not counting the header row). That what you are after?
Sub thisHere()
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)"
End With
End Sub

Not really sure what you meant by

Hello,

[quoted text clipped - 20 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200710/1

 

[AHizon via OfficeKB.com]

Sorry to bother, want to modify the sum formula that you assisted below so
that it sums the cells above minus cell=C2? Is there any way to hard code
the 2nd part so that it's always subtracting from C2 and no other cells?
When I used a Macro to record this function it gives me the following and I
modified it a bit but I'd like it so that it's always Column = C and Row = 2
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.

This will find the last col, the last used row in the column, and
place a formula in the cell below that one that sums all of the cells
above it (not counting the header row). That what you are after?
Sub thisHere()
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)"
End With
End Sub

Not really sure what you meant by

Hello,

[quoted text clipped - 20 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200710/1

 

[JW]

Sorry to bother, want to modify the sum formula that you assisted below so
that it sums the cells above minus cell=C2? Is there any way to hard code
the 2nd part so that it's always subtracting from C2 and no other cells?
When I used a Macro to record this function it gives me the following and I
modified it a bit but I'd like it so that it's always Column = C and Row = 2
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.

This will find the last col, the last used row in the column, and
place a formula in the cell below that one that sums all of the cells
above it (not counting the header row). That what you are after?
Sub thisHere()
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)"
End With
End Sub

Not really sure what you meant by
[quoted text clipped - 20 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200710/1

one way:
Sub thisHere()
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)"
.Offset(1, 0).Formula = _
.Offset(1, 0).Formula & "-$C$2"
End With
End Sub