Average If Problem
 

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

Average If Problem
 

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.

Average If Problem
 

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

Average If Problem
 

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

Average If Problem
 

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.

Average If Problem
 

On Nov 18, 5:49*pm, "joeu2004" wrote:
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

...



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

- Show quoted text -

------------------------------------------------------------------------------------------------------------------------------
It worked:) THANKS Joe! You were right, I did need an absolute
reference.

Average If Problem
 

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.