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]

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))"
For some reason, the code tags aren't working for this.... I have to cut up the line (please ignore the C/Rs)!!

 

[MrKowz]

That isn't even a valid formula. You have way too many arguments for a single IF, you also have mixed A1 style notation with R1C1.

Create a working version of the formula in your worksheet, then copy/paste that into the VBA editor in the following fashion:

Range("X11").Formula = "*your formula*"

 

[gino59]

Thanks, MrKowz. The formula in the worksheet cell is:

=IF($W$3<DATEVALUE("1/1/2011"), SUMIFS(mData[DEPOSIT], mData
[TRANSACTION],$V11,mData[YEAR],lastYear,mData[MONTH],lastMonth)). "lastYear" & "lastMonth" are defined names. mData is the table and the [xxx] are column headers in the mData table. The formula works fine in the sheet(xl07). I'm trying to use that same formula in a module and that's where I think I'm stuck trying to get the syntax right in VBA.

I apologize for the OP - the code tags were messed up maybe by the length of the code line.

Used your suggestion but get the Run-time error '1004': Application-defined or object-defined error.

Sheets("Controls").Activate
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))"

Works fine on the sheet, but my VBA ignorance is keeping me from getting the syntax right so the code line will execute!

Many thanks!
Gino

 

[gino59]

ADVERTISEMENT

Sheets("Controls").Activate
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))"

The whole "=IF(... statement is actually on one line but I can't post it like that here. I think the < sign is messing up the Paste as code here part!

 

[gino59]

Sheets("Controls").Activate
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))"

 

[MrKowz]

ADVERTISEMENT

If you have < or > signs, put a space before and after them. They conflict with HTML.

 

[gino59]

Okay, for whatever reason I'm really having a hard time posting a line of code here. My apologies, but I can't delete the earlier posts!! I put the code in the code tags but it truncates the code line. I put the code in as text and it barfs at the "<" symbol in the code. Gonna try one more time!

The code is:

Range("X11").Formula = _
"=IF(R3C24<DATEVALUE(#1/1/2011#),SUMIFS(mData[DEPOSIT],mdata[TRANSACTION],
R11C23,mData[YEAR],lastYear,mdata[month]lastMonth)"

Fingers crossed this posts correctly!

 

[gino59]

Sheets("Controls").Activate
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))"

 

[MrKowz]

What happens when you try this code? It looks fine to me.

Sheets("Controls").Activate
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))"

Also, you cannot delete, but you can edit posts that you have made in the last 15 minutes by clicking the Edit button in the bottom right of the post. So if something doesn't post right, it is best to use that instead.