Average If Problem

[wesley holtman]

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

 

[wesley holtman]

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

 

[joeu2004]

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.

 

[joeu2004]

PS....

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 -----

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.

 

[wesley holtman]

PS....

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 suspectyou
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 -----

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.- Hide quoted text -

- Show quoted text -