Insert Sumif(s) formula with VBA

swjtx99
swjtx99 used Ask the Experts™
on
Hi,

Please help. Trying to insert these formulas into a Cell via VBA has proven to be a real nightmare.

 Range("M20").Select
 ActiveCell.FormulaR1C1 = "=SUMIF('Sheet1'!F:F," = "&TODAY()-10,'Sheet1'!K:K)"

Range("M21").Select
 ActiveCell.FormulaR1C1 = "=SUMIFS('Sheet1'!K:K,'Sheet1'!F:F,"<"&TODAY()-10,'Sheet1'!F:F,">"&TODAY()-20)"

Thanks in advance,

swjtx99
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel VBA Developer
Top Expert 2014
Commented:
You just need to use the .Formula property and not .FormulaR1C1 (relative references).  Here are your updated formulas:
Range("M20").Formula = "=SUMIF(Sheet1!F:F,TODAY()-10,Sheet1!K:K)"
Range("M21").Formula = "=SUMIFS(Sheet1!K:K,Sheet1!F:F,""< ""&TODAY()-10,Sheet1!F:F,""> ""&TODAY()-20)"

Open in new window

Regards,
-Glenn

Author

Commented:
Hi Glenn,

Thanks! Not sure why R1C1 does not work. I also had to add double quotes as below:

    Range("M20").Formula = "=SUMIF('Sheet1'!F:F,""=""&TODAY()-10,'Sheet1'!K:K)"
    Range("M21").Formula = "=SUMIFS('Sheet1'!K:K,'Sheet1'!F:F,""<""&TODAY()-10,'Sheet1'!F:F,"">""&TODAY()-20)"

Thanks for your help!

swjtx99
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
The .FormulaR1C1 property requires that you specify relative references - in rows and column shift - to the cell in which the formula is entered.  

If you wanted to use that property for your formulas they would look like this instead:
Range("M20").FormulaR1C1 = "=SUMIF(Sheet1!C[-7],TODAY()-10,Sheet1!C[-2])"
Range("M21").FormulaR1C1 = "=SUMIFS(Sheet1!C[-2],Sheet1!C[-7],""<""&TODAY()-10,Sheet1!C[-7],"">""&TODAY()-20)"

Open in new window


-Glenn

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial