Macro Recorder Error

[fredlo2010]

Hello guys

I was wondering if anyone has come across this. I always get my R1C1 formulas from recorded macros. Its easier I think.
Well I was recording a macro today to make my formula and after I finished it I tested and I was surprised it was giving a 1004 error. I had not modified a single line of code.

This had me looking for an answer I couldn't find online. After looking at the code for hours I noticed that some of my formula was cut off at the end of the line Ex:

.....'My Sheet'!" & _
"R[1]C[3].......

would be turn into something like

.....'My She" & _
"R[1]C[3]

[Bob Phillips]

Can you show the exact code?

[fredlo2010]

This is the Code :

ActiveCell.FormulaR1C1 = _
"=IF(OR('CT1+CB-A1'!R[-2]C[-3]<=3,'CT1+CB-A1'!R[-2]C[-11]=""YEL"",'CT1+CB-A1'!R[-2]C[-11]=""GRN/YEL""),"""",IF('CT1+CB-A1'!R[-2]C[-3]>7,IF('CT1+CB-A1'!R[-2]C[-7]<>"""",IF('CT1+CB-A1'!R[-2]C[-2]="""",IF('CT1+CB-A1'!R[-2]C[-3]>=7,('CT1+CB-A1'!R[-2]C[-7]&""""&'CT1+CB-A1'!R[-2]C[-7]),'CT1+CB-A1'!R[-2]C[-7]),('CT1+CB-A1'!R[-2]C[-2]&"" ""&'CT1+CB-A1'!R[-2]C[-7]&""""&'CT1+C" & _
"-2]C[-7])),IF('CT1+CB-A1'!R[-2]C[-2]="""",IF('CT1+CB-A1'!R[-2]C[-3]>=7,('CT1+CB-A1'!R[-2]C[-5]&""""&'CT1+CB-A1'!R[-2]C[-5]),'CT1+CB-A1'!R[-2]C[-5]),('CT1+CB-A1'!R[-2]C[-2]&"" ""&'CT1+CB-A1'!R[-2]C[-5]&""""&'CT1+CB-A1'!R[-2]C[-5]))),IF('CT1+CB-A1'!R[-2]C[-7]<>"""",'CT1+CB-A1'!R[-2]C[-7],'CT1+CB-A1'!R[-2]C[-5])))"

Original Formula:

=IF(OR('CT1+CB-A1'!K4<=3,'CT1+CB-A1'!C4="YEL",'CT1+CB-A1'!C4="GRN/YEL"),"",IF('CT1+CB-A1'!K4>7,IF('CT1+CB-A1'!G4<>"",IF('CT1+CB-A1'!L4="",IF('CT1+CB-A1'!K4>=7,('CT1+CB-A1'!G4&""&'CT1+CB-A1'!G4),'CT1+CB-A1'!G4),('CT1+CB-A1'!L4&" "&'CT1+CB-A1'!G4&""&'CT1+CB-A1'!G4)),IF('CT1+CB-A1'!L4="",IF('CT1+CB-A1'!K4>=7,('CT1+CB-A1'!I4&""&'CT1+CB-A1'!I4),'CT1+CB-A1'!I4),('CT1+CB-A1'!L4&" "&'CT1+CB-A1'!I4&""&'CT1+CB-A1'!I4))),IF('CT1+CB-A1'!G4<>"",'CT1+CB-A1'!G4,'CT1+CB-A1'!I4)))

Here is the link to the original post. http://www.excelforum.com/excel-programming/843442-sorting-and-deleting-rows.html?p=2849737#post2849737

[sassora]

.

[sassora]

I didn't realise how much easier formulas could be to read in VBA style

Not sure where your B-A1'!R[ has gone.

[Bob Phillips]

I first thought it would be too long but when I tried it it worked fine for me (I had to correct a bit of the VBA). But I have Excel 2010, which version do you have?

[fredlo2010]

I am using windows 7 office 2010

A picture is worth a thousand words. The red x means are the points where my code broke automatically causing the error.

http://i48.tinypic.com/rkcx6o.jpg

[Bob Phillips]

Then I cannot see why you get a problem and I don't.

[sassora]

It works if you add B-A1'!R[ near to the second line continuation

i.e.
from
'CT1+C" & _

to
'CT1+CB-A1'!R[" & _

Not sure why excel recording removes that part of the formula though

[Bob Phillips]

BTW, you can make it a tad shorter


ActiveCell.FormulaR1C1 = _
"=IF(OR('CT1+CB-A1'!R[-2]C[-3]<=3,'CT1+CB-A1'!R[-2]C[-11]={""YEL"",""GRN/YEL""})" & _
",""""" & _
",IF('CT1+CB-A1'!R[-2]C[-3]>7" & _
",IF('CT1+CB-A1'!R[-2]C[-7]<>""""" & _
",IF('CT1+CB-A1'!R[-2]C[-2]=""""" & _
",IF('CT1+CB-A1'!R[-2]C[-3]>=7" & _
",'CT1+CB-A1'!R[-2]C[-7]&""""&'CT1+CB-A1'!R[-2]C[-7]" & _
",'CT1+CB-A1'!R[-2]C[-7])" & _
",'CT1+CB-A1'!R[-2]C[-2]&"" ""&'CT1+CB-A1'!R[-2]C[-7]&""""&'CT1+CB-A1'!R[-2]C[-7])" & _
",IF('CT1+CB-A1'!R[-2]C[-2]=""""" & _
",IF('CT1+CB-A1'!R[-2]C[-3]>=7" & _
",'CT1+CB-A1'!R[-2]C[-5]&""""&'CT1+CB-A1'!R[-2]C[-5]" & _
",'CT1+CB-A1'!R[-2]C[-5])" & _
",'CT1+CB-A1'!R[-2]C[-2]&"" ""&'CT1+CB-A1'!R[-2]C[-5]&""""&'CT1+CB-A1'!R[-2]C[-5]))" & _
",IF('CT1+CB-A1'!R[-2]C[-7]<>""""" & _
",'CT1+CB-A1'!R[-2]C[-7]" & _
",'CT1+CB-A1'!R[-2]C[-5])))"


but it is still a horrible formula. Why so complex, what does it do?

[fredlo2010]

Hahaha,

Yah I know its a weird formula. But its not mine I was trying to help someone in another forum. Here (http://www.excelforum.com/excel-programming/843442-sorting-and-deleting-rows.html?p=2849737#post2849737) I think its supposed to show some kind of coded value. There results will be something like "8X:X2:MNS" ( not an exact result I just made this one up)

I am glad I always come to this forum. Even though my problems sometimes cannot be solved I always learn something new and useful. Today's tip break down a complex R1C1 formula into meaningful pasts as you would do in a regular VBA code rather than a column of data like.

And yes sassora the code does work if I complete the formula.

I edited Post #7 to include a picture