How to use excel ranges in excel vba formulas

Question

Using Excel VBA I get syntax error messages when I try option 1 and option 2.

How do I set ranges correctly so that I can use my formula in option 1?

For Option 2, why can't I refer to the ranges just like in normal excel formulas?

This is because I wanted to apply this formula to many rows below this cell. Do I use for loop?

The default code is correct.

'Default
Range("I2").Formula = "=+SUMIFS(R2C2:R434C2,R2C1:R434C1,RC[-1],R2C3:R434C3,""G"")"

'Option 1
    Dim MyRange As Range
    Set MyRange = Range("B2:B434")
    Range("I2").Formula = _
        "=+SUMIFS(" & MyRange & ",R2C1:R434C1,RC[-1],R2C3:R434C3,""G"")"

'Option 2
    Range("I2").Formula = _
        "=+SUMIFS($B$2:$b$434,R2C1:R434C1,RC[-1],R2C3:R434C3,""G"")"

The answer by @user3598756 shows the correct syntax to use. (Notice that option 2 is the same as your default one, but correctly using the FormulaR1C1 property instead of forcing Excel to failover to it.) Your option 2 wasn't working (I think) because you were trying to mix referencing methods within the one formula. — YowE3K

user3598756 user3598756 · Accepted Answer · 2016-12-09T06:31:52

'Default

Range("I2").Formula = "=+SUMIFS(R2C2:R434C2,R2C1:R434C1,RC[-1],R2C3:R434C3,""G"")"

'Option 1

Dim MyRange As Range
Set MyRange = Range("B2:B434")
Range("I2").FormulaR1C1= _
    "=+SUMIFS(" & MyRange.Address(False,False,xlR1C1) & ",R2C1:R434C1,RC[-1],R2C3:R434C3,""G"")"

'Option 2

Range("I2").FormulaR1C1 = _
    "=+SUMIFS(R2C2:R434C2,R2C1:R434C1,RC[-1],R2C3:R434C3,""G"")"

How to use excel ranges in excel vba formulas

1 Answers