Cannot include copy paste in Macro

**Monfreid** · 09-17-2013, 07:44 AM

Hello,
I'm running Excel 2010 on a Windows 7 machine
I'm trying to create a macro and in this macro it include a copy paste of a formula I have made

It look like this in the macro

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=""CNU14627CN"",""myITpc-IRL05"",IF(RC[-3]=""CNU14628Q5"",""myITpc-IRL04"",IF(RC[-3]=""CNU14628Q5"",""myITpc-IRL04"",IF(RC[-3]=""CNU14628NN"",""Inside Screen"",IF(RC[-3]=""CNU2081HWV"",""myITpc-IRL03"",IF(RC[-3]=""CNU2112DYS"",""myITpc-RL06"",IF(RC[-3]=""CNU21241KH"",""Outside Screen"",IF(RC[-3]=""CNU2230LC1"",""myITpc-IRL01"",IF(RC[-3]=""2CE3210WH4"",""On" & _
        """,IF(RC[-3]=""5CB3200VJD"",""On Display"",IF(RC[-3]=""CNU14627M4"",""myITpc-IRL08"",IF(RC[-3]=""CZC2388HVZ"",""NAS Server"",""""))))))))))))"

it works

but now, I have created a new sheet in my Excel doc called Reserved PCs and I want to use it

in Excel it looks like this and it works:

=IF(C2='Reserved PCs'!$A$2,'Reserved PCs'!$B$2,IF(C2='Reserved PCs'!$A$3,'Reserved PCs'!$B$3,IF(C2='Reserved PCs'!$A$4,'Reserved PCs'!$B$4,IF(C2='Reserved PCs'!$A$5,'Reserved PCs'!$B$5,IF(C2='Reserved PCs'!$A$6,'Reserved PCs'!$B$6,IF(C2='Reserved PCs'!$A$7,'Reserved PCs'!$B$7,IF(C2='Reserved PCs'!$A$8,'Reserved PCs'!$B$8,IF(C2='Reserved PCs'!$A$9,'Reserved PCs'!$B$9,IF(C2='Reserved PCs'!$A$10,'Reserved PCs'!$B$10,IF(C2='Reserved PCs'!$A$11,'Reserved PCs'!$B$11,IF(C2='Reserved PCs'!$A$12,'Reserved PCs'!$B$12,IF(C2='Reserved PCs'!$A$13,'Reserved PCs'!$B$13,IF(C2='Reserved PCs'!$A$14,'Reserved PCs'!$B$14,IF(C2='Reserved PCs'!$A$15,'Reserved PCs'!$B$15,IF(C2='Reserved PCs'!$A$16,'Reserved PCs'!$B$16,IF(C2='Reserved PCs'!$A$17,'Reserved PCs'!$B$17,IF(C2='Reserved PCs'!$A$18,'Reserved PCs'!$B$18,IF(C2='Reserved PCs'!$A$19,'Reserved PCs'!$B$19,IF(C2='Reserved PCs'!$A$20,'Reserved PCs'!$B$20,IF(C2='Reserved PCs'!$A$21,'Reserved PCs'!$B$21,IF(C2='Reserved PCs'!$A$22,'Reserved PCs'!$B$22,IF(C2='Reserved PCs'!$A$23,'Reserved PCs'!$B$23,IF(C2='Reserved PCs'!$A$24,'Reserved PCs'!$B$24,IF(C2='Reserved PCs'!$A$25,'Reserved PCs'!$B$25,IF(C2='Reserved PCs'!$A$26,'Reserved PCs'!$B$26,IF(C2='Reserved PCs'!$A$27,'Reserved PCs'!$B$27,IF(C2='Reserved PCs'!$A$28,'Reserved PCs'!$B$29,IF(C2='Reserved PCs'!$A$29,'Reserved PCs'!$B$30,IF(C2='Reserved PCs'!$A$31,'Reserved PCs'!$B$31,"")))))))))))))))))))))))))))))

In the macro it looks like this:

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]='Reserved PCs'!R2C1,'Reserved PCs'!R2C2,IF(RC[-4]='Reserved PCs'!R3C1,'Reserved PCs'!R3C2,IF(RC[-4]='Reserved PCs'!R4C1,'Reserved PCs'!R4C2,IF(RC[-4]='Reserved PCs'!R5C1,'Reserved PCs'!R5C2,IF(RC[-4]='Reserved PCs'!R6C1,'Reserved PCs'!R6C2,IF(RC[-4]='Reserved PCs'!R7C1,'Reserved PCs'!R7C2,IF(RC[-4]='Reserved PCs'!R8C1,'Reserved PCs'!R8C2,IF(RC[-4]='Reserv" & _
        "R9C1,'Reserved PCs'!R9C2,IF(RC[-4]='Reserved PCs'!R10C1,'Reserved PCs'!R10C2,IF(RC[-4]='Reserved PCs'!R11C1,'Reserved PCs'!R11C2,IF(RC[-4]='Reserved PCs'!R12C1,'Reserved PCs'!R12C2,IF(RC[-4]='Reserved PCs'!R13C1,'Reserved PCs'!R13C2,IF(RC[-4]='Reserved PCs'!R14C1,'Reserved PCs'!R14C2,IF(RC[-4]='Reserved PCs'!R15C1,'Reserved PCs'!R15C2,IF(RC[-4]='Reserved PCs'!R16C1," & _
        " PCs'!R16C2,IF(RC[-4]='Reserved PCs'!R17C1,'Reserved PCs'!R17C2,IF(RC[-4]='Reserved PCs'!R18C1,'Reserved PCs'!R18C2,IF(RC[-4]='Reserved PCs'!R19C1,'Reserved PCs'!R19C2,IF(RC[-4]='Reserved PCs'!R20C1,'Reserved PCs'!R20C2,IF(RC[-4]='Reserved PCs'!R21C1,'Reserved PCs'!R21C2,IF(RC[-4]='Reserved PCs'!R22C1,'Reserved PCs'!R22C2,IF(RC[-4]='Reserved PCs'!R23C1,'Reserved PCs" & _
        "F(RC[-4]='Reserved PCs'!R24C1,'Reserved PCs'!R24C2,IF(RC[-4]='Reserved PCs'!R25C1,'Reserved PCs'!R25C2,IF(RC[-4]='Reserved PCs'!R26C1,'Reserved PCs'!R26C2,IF(RC[-4]='Reserved PCs'!R27C1,'Reserved PCs'!R27C2,IF(RC[-4]='Reserved PCs'!R28C1,'Reserved PCs'!R29C2,IF(RC[-4]='Reserved PCs'!R29C1,'Reserved PCs'!R30C2,IF(RC[-4]='Reserved PCs'!R31C1,'Reserved PCs'!R31C2,"""")" & _
        ")))))))))))))))))))"

and if I run the macro, I got an error message (1004) --> Application defined or object defined error and the debug is pointing the formula as the problem

Can you please let me know what could be wrong there?

Moderator Note:

Pls use code tags around your code next time as per forum rules.

Thanks