View Full Version : Macro Recorder Error
fredlo2010
07-06-2012, 07:19 PM
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
07-07-2012, 02:01 AM
Can you show the exact code?
fredlo2010
07-07-2012, 04:40 AM
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
07-07-2012, 06:49 AM
.
sassora
07-07-2012, 07:18 AM
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
07-07-2012, 08:29 AM
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
07-07-2012, 08:37 AM
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
07-07-2012, 08:39 AM
Then I cannot see why you get a problem and I don't.
sassora
07-07-2012, 08:45 AM
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
07-07-2012, 08:50 AM
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
07-07-2012, 09:14 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.