Variable in a Sum() Formula problem

[Xceller]

Any help is much appreciated. Last line of code doesn't work.

Sub GLsort()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(2, 14).Resize(FinalRow, 3).Clear
Cells(2, 15).Resize(FinalRow - 2, 1).FormulaR1C1 = "=TRIM(RC2&RC[-12])"
Cells(2, 14).Resize(FinalRow - 2, 1).FormulaR1C1 = "=IF(IF(LEFT(RC15,5)=""Total"",TRIM(MID(RC15,7,4)),"""")=R[-1]C14,"""",IF(LEFT(RC15,5)=""Total"",TRIM(MID(RC15,7,4)),""""))"
Cells(2, 16).Resize(FinalRow - 2, 1).FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-4])"
Cells(FinalRow, 16).FormulaR1C1 = "=SUM(R[-FinalRow]C:R[-1]C)"

End Sub

 

[iliace]

1. There's probably some number that's supposed to be there before the minus sign.
2. You need to concatenate the variable name.

Cells(FinalRow, 16).FormulaR1C1 = "=SUM(R[-" & FinalRow & "]C:R[-1]C)"

 

[JackDanIce]

Try:

Sub GLsort()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(2, 14).Resize(FinalRow, 3).Clear
Cells(2, 15).Resize(FinalRow - 2, 1).FormulaR1C1 = "=TRIM(RC2&RC[-12])"
Cells(2, 14).Resize(FinalRow - 2, 1).FormulaR1C1 = "=IF(IF(LEFT(RC15,5)=""Total"",TRIM(MID(RC15,7,4)),"""")=R[-1]C14,"""",IF(LEFT(RC15,5)=""Total"",TRIM(MID(RC15,7,4)),""""))"
Cells(2, 16).Resize(FinalRow - 2, 1).FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-4])"
Cells(FinalRow, 16).FormulaR1C1 = "=SUM(R[" & FinalRow & "]C:R[-1]C)"

End Sub

 

[Xceller]

Appreciate your help. It works great.