On Fri, 18 Nov 2011 13:14:21 -0800 (PST), wesley holtman wrote:

On Nov 18, 3:01*pm, Ron Rosenfeld wrote:
On Fri, 18 Nov 2011 11:48:11 -0800 (PST), 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])"

Are the variables in your formula defined as worksheet names? *If they are VBA variables, then you have to concatenate them into the formula string.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hi Ron,

All are VBA varibles and Dim as either range or long. Would you be
willing to show me how to concatenate this formula? I tried
MTDrng1.FormulaR1C1 = "=AVERAGEIF(R" & totalPopRow &
"C21:R[-2]C21,CURRMONNUM,R" & totalPopRow &"C[0]:R[-2]C[0])" but thats
obviously not right!

Dim StartRow As Long, totalPopRow As Long,MTDrng1 As Range, MTDrng2 As
Range, CURRMONNUM As Long
FormRngFR = MTDrow - 2
StartRow = 23
totalPopRow = StartRow - FormRngFR
Set MTDrng1 = SH.Cells(MTDrow, 2).Resize(1, MTD1col)
Set MTDrng2 = SH.Cells(MTDrow, FinNetCol2).Resize(1,
FC - FinNetCol2)
CURRMONNUM = Month(currdate) - 1

For some reason, I did not see this response until a few minutes ago; and I see that joeue has already answered your question.