Trouble with R1C1 & VBA

[gino59]

Hi all,

Keep getting syntax errors on this code.

Range("X11").FormulaR1C1 = _


"=IF($W$3<DATEVALUE(#1/1/2011#),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],R11C23,mData[YEAR],lastYEAR,mData[MONTH],lastMONTH),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],currYEAR,mData[MONTH],lastMONTH))"
I've tried just using .Formula with the actual cell reference V11, but that bombs as well.

I'm just not up to speed on my R1C1 notation!! Any suggestions??

Many thanks!!
Gino

 

[gino59]

Thanks, MrKowz for the HTML tip and the Edit!!

When I run that code it returns the '1004' error message.

Range("W11").Formula = "=SUMIFS(mData[DEPOSIT],mData[ACCOUNT],$V11,mData[YEAR],currYEAR,mData[MONTH],currMONTH)"
 
Range("Y11").Formula = "=SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],currYEAR)"
 
Range("Z11").Formula = "=SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],lastYEAR)"

All those code lines work fine. It's just this one line that keeps bombing out.

Range("X11").Formula = _
"=IF($W$3 < DATEVALUE(#1/1/2011#),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],R11C23,mData[YEAR],lastYEAR,mData[MONTH],lastMONTH),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],currYEAR,mData[MONTH],lastMONTH))"


Not sure what application or object needs to be defined but that's what the error message keeps saying!



THANKS!!

 

[MrKowz]

Are you using .FormulaR1C1 or .Formula with you are testing the one with the IF?

You keep going back and forth, showing .Formula in one post, and then .FormulaR1C1 in another post.

.FormulaR1C1 will enter a formula that is using R1C1 notation. Since you are not using R1C1 notation, this is not the proper method to use.

.Formula will enter a formula that is in A1 notation.

 

[gino59]

Yeah, sorry about that! I keep trying to see if one or the other will work. But I am using Range("X11"). Formula or Range(R11C23).FormulaR1C1. If I can get either to work - it's all good!!! I keep looking at the syntax of the three lines that work and can't seem to see where the "X11" line has an issue!

Thanks for your responses!!

 

[MrKowz]

It is because in the formula, you have an R1C1 reference stuck in the middle:

Range("X11").Formula = _
"=IF($W$3 < DATEVALUE(#1/1/2011#),SUMIFS(mData[DEPOSIT],mData[TRANSACTION][COLOR=red][B],R11C23,[/B][/COLOR]mData[YEAR],lastYEAR,mData[MONTH],lastMONTH),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],currYEAR,mData[MONTH],lastMONTH))"

Try:

Range("X11").Formula = _
"=IF($W$3 < DATEVALUE(#1/1/2011#),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],[COLOR=red][B]$W$11,[/B][/COLOR]mData[YEAR],lastYEAR,mData[MONTH],lastMONTH),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],currYEAR,mData[MONTH],lastMONTH))"

 

[gino59]

Didn't work. Still getting that 1004 error.

Range("X11").Formula = _
"=IF($W$3 < DATEVALUE(#1/1/2011#),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],lastYEAR,mData[MONTH],lastMONTH),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],currYEAR,mData[MONTH],lastMONTH))"

Cheers!

 

[MrKowz]

Since I don't have Excel 2007, I really can't test this out... so we just have to keep going by trial and error.

Try:

Range("X11").Formula = "=IF($W$3 < [COLOR=red][B]DATE(2011,1,1),[/B][/COLOR]SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],lastYEAR,mData[MONTH],lastMONTH),SUMIFS(mData[DEPOSIT],mData[TRANSACTION],$V11,mData[YEAR],currYEAR,mData[MONTH],lastMONTH))"

If it errors again, what error message are you receiving?

 

[gino59]

MrKowz - BRILLIANT!!! That did it!! Took me a few minutes to clean up the mess I had made with changing things in the code over the last few iterations, but it was the flippin' DATE value that was trashing it!

Many, many, many THANKS!!

I keep learning!!!

Cheers!!

Gino