PS....

I wrote:
MTDrng1.FormulaR1C1 =
"=AVERAGEIF(R[" & totalPopRow & "]C21:R[-2]C21," & _
CURRMONNUM & ",R[" & totalPopRow & "]C:R[-2]C)"

(There should be an underscore at the end of the first code line above.)

That is correct for what you wrote originally. But it seems unlikely to me
that R[totalPopRow], which is a relative reference. Instead, I suspect you
want effectively RtotalPopRow, an absolute reference. If so, then try:

MTDrng1.FormulaR1C1 = _
"=AVERAGEIF(R" & totalPopRow & "C21:R[-2]C21," & _
CURRMONNUM & ",R" & totalPopRow & "C:R[-2]C)"


----- original message -----

"joeu2004" wrote in message
...
"wesley holtman" wrote:
Can anyone, really quick, tell me where I am going wrong with the
formula below? I added a watch to all of the variables and all are
returning the correct values, but I keep getting a Run-time error
'1004':
MTDrng1.FormulaR1C1 =
"=AVERAGEIF(R[totalPopRow]C21:R[-2]C21,CURRMONNUM,R[totalPopRow]C[0]:R[-2]C[0])"

Presumably, totalPopRow and CURRMONNUM are VBA variables. Ostensibly, you
might write:

MTDrng1.FormulaR1C1 =
"=AVERAGEIF(R[" & totalPopRow & "]C21:R[-2]C21," & _
CURRMONNUM & ",R[" & totalPopRow & "]C:R[-2]C)"

Of course, AVERAGEIF works only in Excel 2007 and later.

If you have Excel 2003 or earlier, you will still get a runtime error.
You need a very different formula.