Trouble with R1C1 & VBA

gino59

Active Member
Joined
Jul 26, 2010
Messages
496
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
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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)!!
 
Upvote 0
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:

Code:
Range("X11").Formula = "*your formula*"
 
Upvote 0
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
 
Upvote 0
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!
 
Last edited:
Upvote 0
Code:
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))"
 
Upvote 0
If you have < or > signs, put a space before and after them. They conflict with HTML.
 
Upvote 0
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!
 
Upvote 0
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))"
 
Upvote 0
What happens when you try this code? It looks fine to me.

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,222,383
Messages
6,165,661
Members
451,983
Latest member
Raph24

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top