Variable in cell reference

[Super Slueth]

Hello

I'm using the following code to sum a cell range I want to use
variablles but it's not working


strEndCell = ActiveCell.SpecialCells(xlLastCell).Address

strEndCell = Mid(strEndCell, 4)
j = Val(strEndCell)
j = j + 2
str1 = "H" & j
j = j - 2
Range(str1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-j]C:R[-1]C)"


What I'm trying to do is find the last cell in a sheet then go 2 cells
below that and SUM a column

I the SUM formula I'm using "j" as a varable to point to a cell

I'm not sure if I'm going about it in the right manner

Any suggestions please

Many thanks

 

[Bob Phillips]

Dim cell As Range

Set cell = ActiveCell.SpecialCells(xlLastCell)

cell.offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-1]C)"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)