I'm trying to make a table that has subtotals every few columns. My vba code brings and sorts data from another sheet into sections and now i'm trying to write the code to have the subtotal formulas put in. Here is what i have:
Sub Macro21()
Dim FI(1 To 3) As Variant
FI(1) = "Fixed Income"
FI(2) = 10
FI(3) = 21
Sheets("Sheet1").Cells(FI(2), 3).FormulaR1C1 = "=SUBTOTAL(9,R[1]C:R[FI(3)-FI(2)]C)"
End Sub
FI(2) and FI(3) are the beginning and ending rows for this section. I use them in other parts of the macro and they are updated as new items are put under a category.
When I run this it give me an error. Any ideas?
I think you need to build the formula as a string, not make it refer your Variant array. How about:
Sheets("Sheet1").Cells(FI(2), 3).FormulaR1C1 = _
"=SUBTOTAL(9,R[1]C:R[" _
& CStr(FI(3)-FI(2)) _
& "]C)"
This assuming the resulting string is what you'd like to calculate...