Sum a column using fixed start cell and variable end cell

[Guest]

I am trying to use code using a fixed starting point and a variable end point

the code I am using is this

Dim lastrow4 As Long
lastrow4 = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row * -1
Range("D" & lastrow2) = "=SUM(R[lastrow4]C:R[-1]C)"
Range("E" & lastrow2) = "=SUM(R[lastrow4]C:R[-1]C)"

the idea is that lastrow4 is the lastrow, in code if it was a fixed start
and end point it would read

Dim lastrow2 As Long, lastrow4 As Long
lastrow4 = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row * -1
lastrow2 = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row + 1
Range("D" & lastrow2) = "=SUM(R[-63]C:R[-1]C)"


the first cell in the calculation is always D7 or E7 etc but the ending cell
is variable, any help is as alway appreciated

 

[Guest]

I am not really sure if I have totally grasped your problem but have a look
at the following simple code and see if it gives you a hint as to what you
need to do to achieve your required outcome. Put some numbers in column A of
a worksheet and then run the sample macro and then observe how the formula
appears in the last cell. (The fixed cell appears as absolute. Note how this
appears in the VBA code to achieve this.)

The code simply names the last cell and then sums from cell A1 to the named
cell.

VBA needs to insert a name in the worksheet just as you would in the
interactive mode when you want to use that name in a formula in the
worksheet. You cannot use a VBA variable to create a formula in the worksheet
because it's value is lost when the macro finishes running.


Sub Macro1()
'The following finds the last cell with data in column A
'and names it 'LastCell'
'It then offsets to the next cell down and inserts a sum formula
'with the first cell absolute and the last cell being the named cell.

Range("A1").Select
Selection.End(xlDown).Select
ActiveWorkbook.Names.Add Name:="LastCell", RefersToR1C1:=ActiveCell
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R1C1:LastCell)"
End Sub

Regards,

OssieMac

 

[Guest]

I have had another look at your code and I think that it should look
something like this when re written to incorporate the method I previously
posted. Hope it all helps.
NOTE: I have used column A.

Sub Macro2()

'Note: Do not Dim lastrow4 because it is a name on
'the worksheet not a VBA variable

Dim lastrow2 As Range

ActiveWorkbook.Names.Add Name:="lastrow4", _
RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "A").End(xlUp)

Set lastrow2 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp). _
Offset(1, 0)

lastrow2.FormulaR1C1 = "=SUM(R1C1:lastrow4)"

End Sub

Regards,

OssieMac