Excel vba Range & formula Question

[Biz]

Dear All,

I am trying to make and ranges formulas that are not hardcode. fLR is last row in column A.

Instead of using Range("B67:I67") I am trying make it dynamic using
Cells(fLR + 1, 2, Cells(fLR + 1, 9)) but not working. Formula should be
SUM(B50:B66).

How can I make formula and range more variable?

Sub Test()
Dim fLR As Long
fLR = Cells(Rows.Count, "A").End(xlUp).Row
'With Range("B67:I67")before
'Apply Subtotals
With Cells(fLR + 1, 2, Cells(fLR + 1, 9))
    .FormulaR1C1 = "=SUM(R[-17]C:R[-1]C)"
    .Font.Bold = True
End Sub

End With

Biz

 

[njimack]

Use:
Range(Cells(fLR + 1, 2), Cells(fLR + 1, 9))

 

[Biz]

Hi njimack,

Thank you but how about formula

Should be Sum formula in vba code below. SUM(B50:B66)

.FormulaR1C1 = "=SUM(R[-17]C:R[-1]C)"

Biz

 

[njimack]

Does the range you're summing depend on the value of fLR?

 

[Biz]

Does the range you're summing depend on the value of fLR?

Yes, flr =66 becuz on 67 row I put sum formula.

Biz

 

[njimack]

Maybe something like this (I'm not a fan of R1C1 notation):

With Range(Cells(fLR + 1, 2), Cells(fLR + 1, 9))
.Formula = "SUM(B" & fLR - 17 & ":B" & fLR-1 & ")"

 

[Biz]

Mate you mean

.Formula = "=SUM(B" & fLR - 16 & ":B" & fLR & ")"

I solved it another way.
Step 1 Define a ranged name drTitle As Range as A1
Step 2 Defined Dim aRangeDiff As Long, drTitle As Range
Step 3 Created a formula

.FormulaR1C1 = "=SUM(R[-" & aRangeDiff & "]C:R[-1]C)"

This is good I am learning another way.

Biz

 

[Biz]

Sorry

Step 1 Define a ranged name drTitle As Range as B50 is the first cell in addition range.