VBA - FormulaR1C1 Error - #1004 Application-Defined or Object-Defined Error

goodysgotacuda

Board Regular
Joined
Jun 27, 2014
Messages
51
I am trying to apply a formula to a series of cells and recorded the process using the Record Macro method.

The function itself works just fine when I record the Macro, but when I try to run the Excel-Generated code I get a "Application-Defined or Object-Defined Error". I am not sure how Excel would make it's own Macro improperly, but it sure did.


The code that was generated is as follows, I am referencing 1 column as the cells move down and also a table with fixed cell addresses. I assume this is why the generated code varies between the RC[] type reference and the RxCy method of referencing cells.

Code:
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(R[-6]C[-2]<=1300,R[-6]C[-2]>=1000),1650,(R10C21*R[-6]C[-2]^R10C20)+(R11C21*R[-6]C[-2]^R11C20)+(R12C21*R[-6]C[-2]^R12C20)+(R13C21*R[-6]C[-2]^R13C20)+(R14C21*R[-6]C[-2]^R14C20)+(R15C21*R[-6]C[-2]^R15C20)+(R16C21*R[-6]C[-2]^R16C20)+(R17C21*R[-6]C[-2]^R17C20)+(R18C21*R[-6]C[-2]^R18C20)+(R19C21*R[-6]C[-2]^R19C20)+(R20C21*R[-6]C[-2]^R20C20)+(R21C21*R[-6]C[-2]^R21C" & _
        "C21*R[-6]C[-2]^R22C20)+(R23C21*R[-6]C[-2]^R23C20)+(R24C21*R[-6]C[-2]^R24C20)+(R25C21*R[-6]C[-2])+R26C21)"


It is a function that was generated and is a summation of constants multiplied up the 16th power as the table continues, which is why it's so elaborate. I can't seem to change up this code to get it to apply to my series of cells. Any recommendations?


Thank you!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Yep, the Macro Recorder is a great tool. But it's design apparently wasn't fully thought through.
I'm sure the error is in the length of the formula. I think 255 characters is the limit of the length formula you can enter using VBA.
Why the macro recorder is able to actually record it, I don't know.

R1C1 style formulas tend to be longer than A1 style, maybe it would help to use A1 style instead.
What is the actual formula you enter in cell when you record it?
 

goodysgotacuda

Board Regular
Joined
Jun 27, 2014
Messages
51
Yep, the Macro Recorder is a great tool. But it's design apparently wasn't fully thought through.
I'm sure the error is in the length of the formula. I think 255 characters is the limit of the length formula you can enter using VBA.
Why the macro recorder is able to actually record it, I don't know.

R1C1 style formulas tend to be longer than A1 style, maybe it would help to use A1 style instead.
What is the actual formula you enter in cell when you record it?


Thanks for the input Jonmo, the actual formula is as follows: [322 characters]

Code:
=IF(AND($C3<=$P$4,$C3>=$P$3),1650,($U$10*$C3^$T$10)+($U$11*$C3^$T$11)+($U$12*$C3^$T$12)+($U$13*$C3^$T$13)+($U$14*$C3^$T$14)+($U$15*$C3^$T$15)+($U$16*$C3^$T$16)+($U$17*$C3^$T$17)+($U$18*$C3^$T$18)+($U$19*$C3^$T$19)+($U$20*$C3^$T$20)+($U$21*$C3^$T$21)+($U$22*$C3^$T$22)+($U$23*$C3^$T$23)+($U$24*$C3^$T$24)+($U$25*$C3)+$U$26)

Right now my work-around is to just have that formula in an existing cell before the Macro runs, move it to where I want with VBA and AutoFill Down. It works, but certainly not ideal.
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
That can be greatly simplified..

Try

=IF(AND($C3<=$P$4,$C3>=$P$3),1650,SUMPRODUCT($U$10:$U$25*$C3^$T$10:$T$25)+$U$26)
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Looks like I missed the last bit C3 was not powered up to T25...

So that should be
=IF(AND($C3<=$P$4,$C3>=$P$3),1650,SUMPRODUCT($U$10:$U$24*$C3^$T$10:$T$24)+($U$25*$C3)+$U$26)
 

goodysgotacuda

Board Regular
Joined
Jun 27, 2014
Messages
51
Looks like I missed the last bit C3 was not powered up to T25...

So that should be
=IF(AND($C3<=$P$4,$C3>=$P$3),1650,SUMPRODUCT($U$10:$U$24*$C3^$T$10:$T$24)+($U$25*$C3)+$U$26)

Worked perfectly! I haven't used SumProduct before, thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,384
Messages
5,601,320
Members
414,442
Latest member
Oleg

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
Top