Search code examples
excelvbaexcel-formulastring-concatenation

SUM variable value to R1C1 Formula

I'd like to SUM a variable value to a RC Formula, i tried the following:

X = "=SUM(RC[{-24 +" & variable & "}]:RC[-1])"
Solution

  • Make the column calculation independent of the formula and then concatenate it back in. Use application.evaluate for both xlA1 and xlR1C1 syntax.

    Dim f as string, x As double, var as integer
for var = 1 to 9
    f = "=SUM(RC[" & -24 + var  & "]:RC[-1])"
    Debug.Print f
    x = Application.Evaluate("SUM(RC[" & -24 + var  & "]:RC[-1])")
    Debug.Print x
next var