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
– YowE3K, Commented Dec 9, 2016 at 7:16

YowE3K · Accepted Answer · 2016-12-09 07:08:38Z

4

'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"")"

edited Dec 9, 2016 at 7:08

YowE3K

24k7 gold badges28 silver badges45 bronze badges

answered Dec 9, 2016 at 6:31

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

koky Over a year ago

@YowE3K I tried the option 1 but applying one more range. But it does not work for me.

koky Over a year ago

Option 1     Dim irow As Long     irow = Application.WorksheetFunction.Match("No Number", Range("A:A"), 0) - 2     Dim MyRange As Range     Dim haha As String     haha = "B2:B" & irow + 1     Set MyRange = Range(haha)          Dim MyRange1 As Range     Dim haha1 As String     haha1 = "A2:A" & irow + 1     Set MyRange1 = Range(haha1)          Range("I2").Formula = _         "=+SUMIFS(" & MyRange.Address(True, True, xlR1C1) & "," & MyRange1.Address(True, True, xlR1C1) & ",R2C3:R434C3,""G"")"

YowE3K Over a year ago

@koky - In the code in your comment, your SUMIFS seems to be in the format SUMIFS(sum_range,criteria_range1,criteria_range2,criteria2), i.e. you seem to be missing criteria1. (And please use the FormulaR1C1 property when using R1C1 style formula - it will reduce the risk of other problems in the future.)

koky Over a year ago

@YowE3K Thanks for pointing out! So you mean for excel vba, it is advisable to use FormulaR1C1 for cell referencing. The normal excel referencing cannot be used in excel vba right.

YowE3K Over a year ago

@koky - If you are writing a formula in VBA using the normal notation, use the .Formula property. If you are writing a formula in VBA using R1C1 notation, use the .FormulaR1C1 property. R1C1 notation tends to be easier within VBA (because it is so much easier to say RC[-4] than it is to calculate what the current row is, and what the column letter is that is 4 columns left of the current column) but it really depends on what you are doing. (The important thing is, don't try to mix the two notations within one formula. I've seen a lot of people having problems that way.)

Collectives™ on Stack Overflow

How to use excel ranges in excel vba formulas

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related