Average If Problem

W

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

Ron Rosenfeld

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])"
Click to expand...

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

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':
Click to expand...
MTDrng1.FormulaR1C1 =
"=AVERAGEIF(R[totalPopRow]C21:R[-2]C21,CURRMONNUM,R[totalPopRow]C[0]:R[-2]­C[0])"
Click to expand...

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.
Click to expand...
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
 
J

joeu2004

wesley holtman said:
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])"
Click to expand...

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

joeu2004

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

(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 said:
wesley holtman said:
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])"
Click to expand...

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.
Click to expand...
 
W

wesley holtman

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

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




wesleyholtman said:
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])"
Click to expand...
Click to expand...
Presumably, totalPopRow and CURRMONNUM are VBA variables.  Ostensibly, you
might write:
Click to expand...
MTDrng1.FormulaR1C1 =
"=AVERAGEIF(R[" & totalPopRow & "]C21:R[-2]C21," & _
CURRMONNUM & ",R[" & totalPopRow & "]C:R[-2]C)"
Click to expand...
Of course, AVERAGEIF works only in Excel 2007 and later.
Click to expand...
If you have Excel 2003 or earlier, you will still get a runtime error.
You need a very different formula.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
R

Ron Rosenfeld

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':
Click to expand...
MTDrng1.FormulaR1C1 =
"=AVERAGEIF(R[totalPopRow]C21:R[-2]C21,CURRMONNUM,R[totalPopRow]C[0]:R[-2]­C[0])"
Click to expand...

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.
Click to expand...
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

One line is killing my program! 3
Macro to delete row if cells greater than certain value 1
Range.Find problem 3
Six columns randon no.'s no dupes 6
Macro autofills formula when a cell value is changed 4
Copying Charts - Memory Leak - Excel 2003 2
Excel Macro Programing Problem 2
type mismatch error in macro in excel 0

Top