1

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.

Jonaslux
  • 37
  • 4

1 Answers1

3

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

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 1
    I once made a visual representation of "closing a formula" (as Bruce refers to it) in the following post: http://stackoverflow.com/questions/38040259/expected-end-of-statement-error-while-i-am-trying-to-sum-values-of-a-dynamically/38040342#38040342 Hope that helps understanding. – Ralph Aug 16 '16 at 16:33
  • @Ralph - Oh, nice! I'll have to keep that URL for the future, cheers! – BruceWayne Aug 16 '16 at 16:36