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

On Nov 18, 3:01*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Fri, 18 Nov 2011 11:48:11 -0800 (PST), wesley holtman <wesleyholt...@gmail.com> 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

"wesley holtman" <(E-Mail Removed)> 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.

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" <(E-Mail Removed)> wrote in message
news:ja6n5f$3ui$(E-Mail Removed)...
> "wesley holtman" <(E-Mail Removed)> 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.

On Nov 18, 5:49*pm, "joeu2004" <joeu2...@foo.bar> 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 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 -----
>
> "joeu2004" <joeu2...@foo.bar> wrote in message
>
> news:ja6n5f$3ui$(E-Mail Removed)...
>
>
>
> > "wesleyholtman" <wesleyholt...@gmail.com> 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.