时间戳

首页 > 解决方案 > 尝试在单元格中粘贴公式时出现错误 1004

excel - 尝试在单元格中粘贴公式时出现错误 1004

问题描述

我对 VBA 很陌生，我一直在尝试使用宏在单元格中粘贴一些公式。问题是，每次我这样做时，第一个公式都会出现 1004 错误。

我强烈怀疑这是因为引号，但我一生都无法理解出了什么问题。你们有什么想法吗？

Sub PREF()

Dim lastrow As Long
lastrow = Range("'Extract WIN'!A" & Rows.Count).End(xlUp).Row
Dim P2 As Worksheet
Set P2 = Sheets("PRO")

Select Case MsgBox("Do you want to proceed with" & P2.[C2].Value & " ?", vbYesNo, "as datepref")
Case vbYes

Sheets("Extract WIN").Select



    Range("W2" & ":" & "W" & lastrow).FormulaR1C1 = "=IFERROR(DATEVALUE(CONCATENATE(MID(RC[-10];7;2);""/"";MID(RC[-10];5;2);""/"";MID(RC[-10];1;4)));TEXT(,))"
    Range("Y2" & ":" & "Y" & lastrow).FormulaR1C1 = "=IFERROR(DATEVALUE(CONCATENATE(MID(RC[-10];7;2);""/"";MID(RC[-10];5;2);""/"";MID(RC[-10];1;4)));TEXT(,))"
    Range("AA2" & ":" & "AA" & lastrow).FormulaR1C1 = "=IFERROR(IF(AND(Provision!R2C3-RC[-4]<366;RC[-18]>0);RC[-18];0);TEXT(,))"
    Range("AB2" & ":" & "AB" & lastrow).FormulaR1C1 = "=IFERROR(RC[-1]*RC[-18];TEXT(,))"
    Range("AC2" & ":" & "AC" & lastrow).FormulaR1C1 = "=IF(AND(RC[-2]=0;RC[-20]>0;RC[-4]>Provision!R2C6;ISNA(VLOOKUP(RIGHT(TEXT(RC[-25];""000#####"");4);Provision!R7C17:R101C18;1;FAUX))=FAUX);1;0)"
    Range("AD2" & ":" & "AD" & lastrow).FormulaR1C1 = "=RC[-1]*RC[-20]"
    Range("AE2" & ":" & "AE" & lastrow).FormulaR1C1 = "=IFERROR(RC[-22]-RC[-4]-RC[-2];TEXT(,))"
    Range("AF2" & ":" & "AF" & lastrow).FormulaR1C1 = "=IF(AND(RC[-20]>0;RC[-1]>0);ROUND(MIN(RC[-20]*12;RC[-1]);0);0)"
    Range("AG2" & ":" & "AG" & lastrow).FormulaR1C1 = "=RC[-1]*RC[-23]"
    Range("AH2" & ":" & "AH" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-18]"
    Range("AI2" & ":" & "AI" & lastrow).FormulaR1C1 = "=IFERROR(RC[-4]-RC[-3];TEXT(,))"
    Range("AJ2" & ":" & "AJ" & lastrow).FormulaR1C1 = "=IF(RC[-24]>0;ROUND(MIN(RC[-24]*12;RC[-1]);0);0)"
    Range("AK2" & ":" & "AK" & lastrow).FormulaR1C1 = "=RC[-1]*RC[-27]"
    Range("AL2" & ":" & "AL" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-21]"
    Range("AM2" & ":" & "AM" & lastrow).FormulaR1C1 = "=IFERROR(RC[-4]-RC[-3];TEXT(,))"
    Range("AN2" & ":" & "AN" & lastrow).FormulaR1C1 = "=IF(AND(RC[-16]>Provision!R2C7;RC[-28]>=0);RC[-1];0)"
    Range("AO2" & ":" & "AO" & lastrow).FormulaR1C1 = "=IFERROR(RC[-1]*RC[-31];TEXT(,))"
    Range("AP2" & ":" & "AP" & lastrow).FormulaR1C1 = "=IF(RC[-18]=TEXT(,);0;IF(AND(X2<Provision!R2C7;RC[-3]>0);RC[-3];0))"
    Range("AQ2" & ":" & "AQ" & lastrow).FormulaR1C1 = "=RC[-1]*RC[-33]"
    Range("AR2" & ":" & "AR" & lastrow).FormulaR1C1 = "=IF(RC[-20]="""";RC[-5];0)"
    Range("AS2" & ":" & "AS" & lastrow).FormulaR1C1 = "=IFERROR(RC[-1]*RC[-35];TEXT(,))"
    Range("AT2" & ":" & "AT" & lastrow).FormulaR1C1 = "=IFERROR(RC[-6]+RC[-4]+RC[-2];TEXT(,))"
    Range("AU2" & ":" & "AU" & lastrow).FormulaR1C1 = "=IFERROR(RC[-6]+RC[-4]+RC[-2];TEXT(,))"
    Range("AV2" & ":" & "AV" & lastrow).FormulaR1C1 = "=IFERROR(RC[-2]-RC[-30];TEXT(,))"
    Range("AX2" & ":" & "AX" & lastrow).FormulaR1C1 = "=IFERROR(RC[-13]*0,5;TEXT(,))"
    Range("AY2" & ":" & "AY" & lastrow).FormulaR1C1 = "=IFERROR(RC[-4]*0,9;TEXT(,))"
    Range("AZ2" & ":" & "AZ" & lastrow).FormulaR1C1 = "=IFERROR(RC[-2]+RC[-1];TEXT(,))"
    Range("BA2" & ":" & "BA" & lastrow).FormulaR1C1 = "=IFERROR(RANK(RC[-1];RC:RC;0);TEXT(,))"
    Columns("AA:AZ").NumberFormat = "#,##0"
    Columns("W:BA").EntireColumn.AutoFit


    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("AI2").Select

Sheets("PRO").Select


Case vbNo
P2.[C2].Select

End Select

End Sub

对于代码墙，我很抱歉，但我不知道我在哪里做错了，建议给你们完整的上下文

标签： excelvba

解决方案

您需要替换“;” 用常规的","。

例如，以这行代码为例：

Range("W2:" & "W" & LastRow).FormulaR1C1 = "=IFERROR(DATEVALUE(CONCATENATE(MID(RC[-10];7;2);""/"";MID(RC[-10];5;2);""/"";MID(RC[-10];1;4)));TEXT(,))"

并将其更改为：

Range(Cells(2, 1), Cells(2, LastRow)).FormulaR1C1 = "=IFERROR(DATEVALUE(CONCATENATE(MID(RC[-10],7,2),""/"",MID(RC[-10],5,2),""/"",MID(RC[-10],1,4))),TEXT(,))"

另外，请记住我对这条线做了一些更改。我对其进行了更改，以便它使用单元格而不是范围。我发现 Cells 更容易使用并提供更好的可读性，因为我们不必加入任何字符串。

无论哪种方式都很好，它只是归结为偏好。

推荐阅读