Introduce variables in recorded VBA macro

Question

I recorded part of this macro in Excel:

    Cells(x, i).Select
        Selection.FormulaArray = _
        "=SQRT((MMULT(MMULT(TRANSPOSE(R2C14:R9C14),'Monthly Covariance y1'!R[12]C[0]:R[19]C[7]),'Portfolio Vola per Month'!R2C14:R9C14)))"

In the middle term " 'Monthly Covariance y1'!R[12]C[0]:R[19]C[7])" I want to express the bold numbers as variables. 0 should be j and 7 should be j+7.

When I try to replace the hard numbers by variables, Excel returns "Unable to set FormulaArray property of the Range class".

Any idea on how I can work around this problem?

Many thanks.

Answer 1

Just close the formula, add variables, and open again:

"=SQRT((MMULT(MMULT(TRANSPOSE(R2C14:R9C14),'Monthly Covariance y1'!R[12]C["& j & "]:R[19]C["& j + 7 & "]),'Portfolio Vola per Month'!R2C14:R9C14)))"

By "close the formula", I mean close with a ", then use & to connect the variables, then "reopen" with ". Simple example, with j being a row number, say 5:

myRange.FormulaR1C1 = "=Sum(A" & j & ":A" & j & ")"

is equivalent to

myRange.FormulaR1C1 = "=Sum(A5:A5)"

Also, this is another issue altogether, but try to avoid using .Select