运行时错误"1004":对象“范围"的方法"FormulaR1C1"失败 [英] Run-time error '1004': Method 'FormulaR1C1' of object 'Range' failed

问题描述

我正在尝试创建一个简单的宏，该宏基本上是添加一个新的列，将其命名，粘贴公式并填写.我得到的错误与主题相同:

运行时错误'1004':对象'范围'的方法'FormulaR1C1'失败

这是公式:

  = IF(OR($ G2 = DATA！$ L $ 3; G2 = DATA！$ L $ 4; $ G2 = DATA！$ L $ 5; $ G2 = DATA！$ L $ 6; $ G2 = DATA$ L $ 7; $ G2 = DATA！$ L $ 8; $ G2 = DATA！$ L $ 9; $ G2 = DATA！$ L $ 10); DATA！$ L $ 2; IF(OR($ G2 = DATA！$ M$ 3; G2 = DATA！$ M $ 4; $ G2 = DATA！$ M $ 5; $ G2 = DATA！$ M $ 6; $ G2 = DATA！$ M $ 7; $ G2 = DATA！$ M $ 8; $ G2 = DATA$ M $ 9; $ G2 = DATA！$ M $ 10); DATA！$ M $ 2; IF(OR($ G2 = DATA！$ N $ 3; G2 = DATA！$ N $ 4; $ G2 = DATA！$ N $ 5; $ G2 = DATA！$ N $ 6; $ G2 = DATA！$ N $ 7; $ G2 = DATA！$ N $ 8; $ G2 = DATA！$ N $ 9; $ G2 = DATA！$ N $ 10); DATA！$N $ 2; IF(OR($ G2 = DATA！$ O $ 3; G2 = DATA！$ O $ 4; $ G2 = DATA！$ O $ 5; $ G2 = DATA！$ O $ 6; $ G2 = DATA！$ O $ 7; $ G2 = DATA！$ O $ 8; $ G2 = DATA！$ O $ 9; $ G2 = DATA！$ O $ 10); DATA！$ O $ 2; IF(OR($ G2 = DATA！$ P $ 3; G2 =DATA！$ P $ 4; $ G2 = DATA！$ P $ 5; $ G2 = DATA！$ P $ 6; $ G2 = DATA！$ P $ 7; $ G2 = DATA！$ P $ 8; $ G2 = DATA！$ P $ 9; $ G2 = DATA！$ P $ 10); DATA！$ P $ 2;"OTHER")))))) 

还有宏:

  Sub Macro16()''Macro16宏''Selection.Insert Shift:= xlToRight，CopyOrigin:= xlFormatFromLeftOrAbove范围("I1").选择ActiveCell.FormulaR1C1 ="CSS团队"范围("I2").选择ActiveCell.FormulaR1C1 = _"= IF(OR(RC7 = DATA！R3C12，RC [-2] = DATA！R4C12，RC7 = DATA！R5C12，RC7 = DATA！R6C12，RC7 = DATA！R7C12，RC7 = DATA！R8C12，RC7 = DATA！R9C12，RC7 = DATA！R10C12)，DATA！R2C12，IF(OR(RC7 = DATA！R3C13，RC [-2] = DATA！R4C13，RC7 = DATA！R5C13，RC7 = DATA！R6C13，RC7 = DATA！R7C13，RC7 = DATA！R8C13，RC7 = DATA！R9C13，RC7 = DATA！R10C13)，DATA！R2C13，IF(OR(RC7 = DATA！R3C14，RC [-2] = DATA！R4C14，RC7 = DATA！R5C14，RC7 = DATA！R6C14，RC7 = DATA！R7C& _"ATA！R8C14，RC7 = DATA！R9C14，RC7 = DATA！R10C14)，DATA！R2C14，IF(OR(RC7 = DATA！R3C15，RC [-2] = DATA！R4C15，RC7 = DATA！R5C15，RC7 =DATA！R6C15，RC7 = DATA！R7C15，RC7 = DATA！R8C15，RC7 = DATA！R9C15，RC7 = DATA！R10C15)，DATA！R2C15，IF(OR(RC7 = DATA！R3C16，RC [-2] = DATAR4C16，RC7 = DATA！R5C16，RC7 = DATA！R6C16，RC7 = DATA！R7C16，RC7 = DATA！R8C16，RC7 = DATA！R9C16，RC7 = DATA！R10C16)，DATA！R2C16，" OTHER")))))范围("I2").选择Selection.AutoFill目的地:= Range("I2:I21445")范围("I2:I21445").选择结束子 

解决方案

在663个字符处，您的公式可能太长，无法作为

I am trying to create a simple macro which basically is adding a new colum, naming it, pasting the formula and filling down. The error I get is as in the subject:

Run-time error '1004': Method 'FormulaR1C1' of object 'Range' failed

Here is the formula:

=IF(OR($G2=DATA!$L$3;G2=DATA!$L$4;$G2=DATA!$L$5;$G2=DATA!$L$6;$G2=DATA!$L$7;$G2=DATA!$L$8;$G2=DATA!$L$9;$G2=DATA!$L$10);DATA!$L$2; IF(OR($G2=DATA!$M$3;G2=DATA!$M$4;$G2=DATA!$M$5;$G2=DATA!$M$6;$G2=DATA!$M$7;$G2=DATA!$M$8;$G2=DATA!$M$9;$G2=DATA!$M$10);DATA!$M$2; IF(OR($G2=DATA!$N$3;G2=DATA!$N$4;$G2=DATA!$N$5;$G2=DATA!$N$6;$G2=DATA!$N$7;$G2=DATA!$N$8;$G2=DATA!$N$9;$G2=DATA!$N$10);DATA!$N$2; IF(OR($G2=DATA!$O$3;G2=DATA!$O$4;$G2=DATA!$O$5;$G2=DATA!$O$6;$G2=DATA!$O$7;$G2=DATA!$O$8;$G2=DATA!$O$9;$G2=DATA!$O$10);DATA!$O$2; IF(OR($G2=DATA!$P$3;G2=DATA!$P$4;$G2=DATA!$P$5;$G2=DATA!$P$6;$G2=DATA!$P$7;$G2=DATA!$P$8;$G2=DATA!$P$9;$G2=DATA!$P$10);DATA!$P$2; "OTHER")))))

And the macro:

Sub Macro16()
'
' Macro16 Macro
'

'
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "CSS Team"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(RC7=DATA!R3C12,RC[-2]=DATA!R4C12,RC7=DATA!R5C12,RC7=DATA!R6C12,RC7=DATA!R7C12,RC7=DATA!R8C12,RC7=DATA!R9C12,RC7=DATA!R10C12),DATA!R2C12, IF(OR(RC7=DATA!R3C13,RC[-2]=DATA!R4C13,RC7=DATA!R5C13,RC7=DATA!R6C13,RC7=DATA!R7C13,RC7=DATA!R8C13,RC7=DATA!R9C13,RC7=DATA!R10C13),DATA!R2C13, IF(OR(RC7=DATA!R3C14,RC[-2]=DATA!R4C14,RC7=DATA!R5C14,RC7=DATA!R6C14,RC7=DATA!R7C" & _
        "ATA!R8C14,RC7=DATA!R9C14,RC7=DATA!R10C14),DATA!R2C14, IF(OR(RC7=DATA!R3C15,RC[-2]=DATA!R4C15,RC7=DATA!R5C15,RC7=DATA!R6C15,RC7=DATA!R7C15,RC7=DATA!R8C15,RC7=DATA!R9C15,RC7=DATA!R10C15),DATA!R2C15, IF(OR(RC7=DATA!R3C16,RC[-2]=DATA!R4C16,RC7=DATA!R5C16,RC7=DATA!R6C16,RC7=DATA!R7C16,RC7=DATA!R8C16,RC7=DATA!R9C16,RC7=DATA!R10C16),DATA!R2C16, ""OTHER"")))))"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I21445")
    Range("I2:I21445").Select
End Sub

解决方案

At 663 characters, your formula may be too long to be stuffed into a cell as the Range.Formula property or Range.FormulaR1C1 property but of primary concern is the fact that VBA is very EN-US centric and your formula must be passed into the property with commas as list separators, not semi-coloons regardless of the fact that your computer's regional system specifies the semi-colon as the list separator character and that you use a semi-colon when typing a formula into the worksheet.

=IF(OR($G2=DATA!$L$3,G2=DATA!$L$4,$G2=DATA!$L$5,$G2=DATA!$L$6,$G2=DATA!$L$7,$G2=DATA!$L$8,$G2=DATA!$L$9,$G2=DATA!$L$10), DATA!$L$2, 
 IF(OR($G2=DATA!$M$3,G2=DATA!$M$4,$G2=DATA!$M$5,$G2=DATA!$M$6,$G2=DATA!$M$7,$G2=DATA!$M$8,$G2=DATA!$M$9,$G2=DATA!$M$10), DATA!$M$2, 
 IF(OR($G2=DATA!$N$3,G2=DATA!$N$4,$G2=DATA!$N$5,$G2=DATA!$N$6,$G2=DATA!$N$7,$G2=DATA!$N$8,$G2=DATA!$N$9,$G2=DATA!$N$10), DATA!$N$2, 
 IF(OR($G2=DATA!$O$3,G2=DATA!$O$4,$G2=DATA!$O$5,$G2=DATA!$O$6,$G2=DATA!$O$7,$G2=DATA!$O$8,$G2=DATA!$O$9,$G2=DATA!$O$10), DATA!$O$2, 
 IF(OR($G2=DATA!$P$3,G2=DATA!$P$4,$G2=DATA!$P$5,$G2=DATA!$P$6,$G2=DATA!$P$7,$G2=DATA!$P$8,$G2=DATA!$P$9,$G2=DATA!$P$10), DATA!$P$2,
   "OTHER")))))

If you absolutely need to use semi-colons, you can apply the remi-colon separated formula with the Range.FormulaLocal property.

-Addendum:

I've cut your formula down to 98 characters in xlA1 style.

=IFERROR(INDEX(Data!L$2:P$2, 1, AGGREGATE(15, 6, COLUMN($A:$E)/(Data!$L$3:$P$10=G2), 1)), "OTHER")

Your original formula is in F2 filled down to F8. The proposed formula above is in E2 filled down to E8.

      

这篇关于运行时错误"1004":对象“范围"的方法"FormulaR1C1"失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！