文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >Excel VBA:运行时错误“1004”应用程序定义或对象定义错误

问Excel VBA:运行时错误“1004”应用程序定义或对象定义错误
EN

Stack Overflow用户
提问于 2018-08-15 12:31:21
回答 1查看 470关注 0票数 0

我为我们在工作地点使用的检查表编写了一段代码--它已经工作了6个月，没有任何问题，现在突然间，我得到了由这一行引起的“运行时错误'1004‘应用程序定义的或对象定义的错误”：

代码语言：javascript
运行
复制
Range("U" & ar).Formula = "=IF('PCA & Feedback'!Q" & ar & "=""Y"", 'PCA & Feedback'R" & ar & ", IF('PCA & Feedback'!N" & ar & "="""", 'PCA & Feedback'!M" & ar & ", 'PCA & Feedback'!N" & ar & "))"

我使用的工作表是受保护的，但如您所见，它们在宏开始时是不受保护的。代码放在一个模块中，所以这也不应该是问题所在。我还尝试在每个范围之前指定工作表，但这对我也不适用(例如，Ov.Range("U“&ar).)。

在这条线之前，一切都很好，而对于我的生活，我不知道是什么原因造成了这个错误。任何您能提供的帮助都是非常感谢的！

代码语言：javascript
运行
复制
Sub InsertRows()

Dim Ov, Bo, Bi, SU, Co, PF, ws As Worksheet
Dim ar As Long
Dim ce As Range
Set Ov = Sheets("Overview")
Set Bo = Sheets("Booking")
Set Bi = Sheets("Billing")
Set SU = Sheets("Set Up")
Set Co = Sheets("Copy")
Set PF = Sheets("PCA & Feedback")

ar = ActiveCell.Row

Application.ScreenUpdating = False

For Each ws In Worksheets
ws.Unprotect
Next

Bo.Activate
Rows(ar & ":" & ar).EntireRow.Insert
Range("E" & ar).Formula = "=VLOOKUP(C" & ar & ",'Deal Numbers 2015'!$A$2:$B$85,2,FALSE)"
Range("Q" & ar).Formula = "=VLOOKUP(H" & ar & ", Lists!$A$18:$B$25, 2, FALSE)"
Range("R" & ar).Formula = "=WORKDAY(I" & ar & ", -Q" & ar & ")"

Bi.Activate
Rows(ar & ":" & ar).EntireRow.Insert

Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=Booking!D" & ar
Range("E" & ar).Formula = "=Booking!I" & ar
Range("F" & ar).Formula = "=Booking!J" & ar
Range("G" & ar).Formula = "=Booking!P" & ar
Range("H" & ar).Formula = "=Booking!F" & ar
Range("I" & ar).Formula = "=Booking!G" & ar
Range("M" & ar).Formula = "=IF(AND(L" & ar & "=""Y"", N" & ar & " = """"), ""Y"", ""N"")"

SU.Activate
Rows(ar & ":" & ar).EntireRow.Insert

Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=Booking!D" & ar
Range("E" & ar).Formula = "=Booking!I" & ar
Range("F" & ar).Formula = "=Booking!J" & ar
Range("G" & ar).Formula = "=Booking!P" & ar
Range("L" & ar).Formula = "=WORKDAY(Booking!I" & ar & ", -8)"
Range("N" & ar).Formula = "=IF(AND(COUNTA(O" & ar & ")=1, ISBLANK(M" & ar & ")), ""Y"", ""N"")"

Co.Activate
Rows(ar & ":" & ar).EntireRow.Insert

Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=Booking!D" & ar
Range("E" & ar).Formula = "=Booking!I" & ar
Range("F" & ar).Formula = "=Booking!J" & ar
Range("G" & ar).Formula = "='Set Up'!K" & ar
Range("H" & ar).Formula = "='Set Up'!O" & ar
Range("J" & ar).Formula = "=WORKDAY(Booking!I" & ar & ", -7)"
Range("L" & ar).Formula = "=IF(AND(ISNUMBER(SEARCH(""Copy Attached"", M" & ar & ")), ISBLANK(K" & ar & ")), ""Y"", ""N"")"
Range("N" & ar).Formula = "=IF(AND(M" & ar & "=""Copy Attached"", OR(O" & ar & "=""N"", O" & ar & " = """")), ""Y"", ""N"")"

Range("I" & ar).Value = "Awaiting Set Up"
Range("M" & ar).Value = "Copy Not Attached"

PF.Activate
Rows(ar & ":" & ar).EntireRow.Insert
Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=Booking!D" & ar
Range("E" & ar).Formula = "=Booking!J" & ar
Range("F" & ar).Formula = "=WORKDAY(Booking!J" & ar & ", 8)"
Range("H" & ar).Formula = "=IF(AND(G" & ar & "=""Y"", I" & ar & "=""""), ""Y"", ""N"")"
Range("M" & ar).Formula = "=WORKDAY(F" & ar & ", 10)"
Range("P" & ar).Formula = "=WORKDAY(O" & ar & ", 10)"

  'Adding Row to Sheet
Ov.Activate

Rows(ar & ":" & ar).EntireRow.Insert

'Adding formulas to Sheet
Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=IF(Booking!D" & ar & "=0, """", Booking!D" & ar & "&""-""&Booking!K" & ar & ")"
Range("E" & ar).Formula = "=Booking!F" & ar
Range("F" & ar).Formula = "=Booking!I" & ar
Range("G" & ar).Formula = "=Booking!J" & ar
Range("H" & ar).Formula = "=Booking!H" & ar
Range("I" & ar).Formula = "=ISBLANK(Booking!P" & ar & ")"
Range("J" & ar).Formula = "=IF(D" & ar & "="""", """", IF(Booking!M" & ar & "=""Y"", ""On SF"", ""Not on SF""))"
Range("K" & ar).Formula = "=IF(D" & ar & "="""", """", IF(I" & ar & "=TRUE, J" & ar & ", Booking!P" & ar & "))"
Range("L" & ar).Formula = "=ISBLANK('Set Up'!K" & ar & ")"
Range("M" & ar).Formula = "=Booking!R" & ar
Range("N" & ar).Formula = "=IF(Booking!G" & ar & "=""Y"", 1, 0)"
Range("O" & ar).Formula = "=IF(Booking!N" & ar & "=""Closed Won"", 1, 0)"
Range("P" & ar).Formula = "=IF(D" & ar & "="""", """", IF(SUM(N" & ar & ":O" & ar & ")<2, ""N"", IF(SUM(N" & ar & ":O" & ar & ")=2, ""Y"")))"
Range("Q" & ar).Formula = "=IF(D" & ar & "="""", """", IF(L" & ar & "=TRUE, ""Requested"", 'Set Up'!K" & ar & "))"
Range("R" & ar).Formula = "=IF(D" & ar & "="""", """", IF(Copy!M" & ar & "=""Copy Not Attached"", Copy!I" & ar & ", Copy!M" & ar & "))"
Range("S" & ar).Formula = "=Copy!J" & ar
Range("T" & ar).Formula = "='PCA & Feedback'!I" & ar
Range("U" & ar).Formula = "=IF('PCA & Feedback'!Q" & ar & "=""Y"", 'PCA & Feedback'R" & ar & ", IF('PCA & Feedback'!N" & ar & "="""", 'PCA & Feedback'!M" & ar & ", 'PCA & Feedback'!N" & ar & "))"
Range("V" & ar).Formula = "=IF(COUNTBLANK(Booking!S" & ar & ")+COUNTBLANK('Set Up'!R" & ar & ")+COUNTBLANK(Copy!P" & ar & ")=3, """", Booking!S" & ar & "&""; ""&'Set Up'!R" & ar & "&""; ""&Copy!P" & ar & ")"

For Each ws In Worksheets
ws.Protect , DrawingObjects:=False, AllowFiltering:=True
Next

Bo.Activate
Range("B" & ar).Activate

Application.ScreenUpdating = True

End Sub
excel
vba
runtime-error
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-08-15 13:04:27

您的具体问题是公式缺少了!在PCA & Feedback'R & ar

Range("U" & ar).Formula = "=IF('PCA & Feedback'!Q" & ar & "=""Y"", 'PCA & Feedback'R" & ar & ", IF('PCA & Feedback'!N" & ar & "="""", 'PCA & Feedback'!M" & ar & ", 'PCA & Feedback'!N" & ar & "))"

我已经重写了包含With的代码，并消除了激活每个工作表的需要。

我也评论了屏幕更新-不应该真的需要它，因为床单不再被激活。

注释1:，我在这里编写了ThisWorkbook，我指的是代码所在的工作簿。

注释2:当在With...End With块中使用单元格引用时，使用.启动引用。

所以.Range("E" & ar)而不仅仅是Range("E" & ar)

代码语言：javascript
运行
复制
Sub InsertRows()

    Dim Ov As Worksheet, Bo As Worksheet, Bi As Worksheet, SU As Worksheet
    Dim Co As Worksheet, PF As Worksheet, ws As Worksheet
    Dim ar As Long
    Dim ce As Range

    With ThisWorkbook
        Set Ov = .Sheets("Overview")
        Set Bo = .Sheets("Booking")
        Set Bi = .Sheets("Billing")
        Set SU = .Sheets("Set Up")
        Set Co = .Sheets("Copy")
        Set PF = .Sheets("PCA & Feedback")
    End With

    ar = ActiveCell.Row

    'Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect
    Next

    'Bo.Activate
    With Bo
        .Rows(ar & ":" & ar).EntireRow.Insert
        .Range("E" & ar).Formula = "=VLOOKUP(C" & ar & ",'Deal Numbers 2015'!$A$2:$B$85,2,FALSE)"
        .Range("Q" & ar).Formula = "=VLOOKUP(H" & ar & ", Lists!$A$18:$B$25, 2, FALSE)"
        .Range("R" & ar).Formula = "=WORKDAY(I" & ar & ", -Q" & ar & ")"
    End With

    'Bi.Activate
    With Bi
        .Rows(ar & ":" & ar).EntireRow.Insert

        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=Booking!D" & ar
        .Range("E" & ar).Formula = "=Booking!I" & ar
        .Range("F" & ar).Formula = "=Booking!J" & ar
        .Range("G" & ar).Formula = "=Booking!P" & ar
        .Range("H" & ar).Formula = "=Booking!F" & ar
        .Range("I" & ar).Formula = "=Booking!G" & ar
        .Range("M" & ar).Formula = "=IF(AND(L" & ar & "=""Y"", N" & ar & " = """"), ""Y"", ""N"")"
    End With

    'SU.Activate
    With SU
        .Rows(ar & ":" & ar).EntireRow.Insert

        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=Booking!D" & ar
        .Range("E" & ar).Formula = "=Booking!I" & ar
        .Range("F" & ar).Formula = "=Booking!J" & ar
        .Range("G" & ar).Formula = "=Booking!P" & ar
        .Range("L" & ar).Formula = "=WORKDAY(Booking!I" & ar & ", -8)"
        .Range("N" & ar).Formula = "=IF(AND(COUNTA(O" & ar & ")=1, ISBLANK(M" & ar & ")), ""Y"", ""N"")"
    End With

    'Co.Activate
    With Co
        .Rows(ar & ":" & ar).EntireRow.Insert

        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=Booking!D" & ar
        .Range("E" & ar).Formula = "=Booking!I" & ar
        .Range("F" & ar).Formula = "=Booking!J" & ar
        .Range("G" & ar).Formula = "='Set Up'!K" & ar
        .Range("H" & ar).Formula = "='Set Up'!O" & ar
        .Range("J" & ar).Formula = "=WORKDAY(Booking!I" & ar & ", -7)"
        .Range("L" & ar).Formula = "=IF(AND(ISNUMBER(SEARCH(""Copy Attached"", M" & ar & ")), ISBLANK(K" & ar & ")), ""Y"", ""N"")"
        .Range("N" & ar).Formula = "=IF(AND(M" & ar & "=""Copy Attached"", OR(O" & ar & "=""N"", O" & ar & " = """")), ""Y"", ""N"")"

        .Range("I" & ar).Value = "Awaiting Set Up"
        .Range("M" & ar).Value = "Copy Not Attached"
    End With

    'PF.Activate
    With PF
        .Rows(ar & ":" & ar).EntireRow.Insert
        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=Booking!D" & ar
        .Range("E" & ar).Formula = "=Booking!J" & ar
        .Range("F" & ar).Formula = "=WORKDAY(Booking!J" & ar & ", 8)"
        .Range("H" & ar).Formula = "=IF(AND(G" & ar & "=""Y"", I" & ar & "=""""), ""Y"", ""N"")"
        .Range("M" & ar).Formula = "=WORKDAY(F" & ar & ", 10)"
        .Range("P" & ar).Formula = "=WORKDAY(O" & ar & ", 10)"
    End With

      'Adding Row to Sheet
    'Ov.Activate

    With Ov

        .Rows(ar & ":" & ar).EntireRow.Insert

        'Adding formulas to Sheet
        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=IF(Booking!D" & ar & "=0, """", Booking!D" & ar & "&""-""&Booking!K" & ar & ")"
        .Range("E" & ar).Formula = "=Booking!F" & ar
        .Range("F" & ar).Formula = "=Booking!I" & ar
        .Range("G" & ar).Formula = "=Booking!J" & ar
        .Range("H" & ar).Formula = "=Booking!H" & ar
        .Range("I" & ar).Formula = "=ISBLANK(Booking!P" & ar & ")"
        .Range("J" & ar).Formula = "=IF(D" & ar & "="""", """", IF(Booking!M" & ar & "=""Y"", ""On SF"", ""Not on SF""))"
        .Range("K" & ar).Formula = "=IF(D" & ar & "="""", """", IF(I" & ar & "=TRUE, J" & ar & ", Booking!P" & ar & "))"
        .Range("L" & ar).Formula = "=ISBLANK('Set Up'!K" & ar & ")"
        .Range("M" & ar).Formula = "=Booking!R" & ar
        .Range("N" & ar).Formula = "=IF(Booking!G" & ar & "=""Y"", 1, 0)"
        .Range("O" & ar).Formula = "=IF(Booking!N" & ar & "=""Closed Won"", 1, 0)"
        .Range("P" & ar).Formula = "=IF(D" & ar & "="""", """", IF(SUM(N" & ar & ":O" & ar & ")<2, ""N"", IF(SUM(N" & ar & ":O" & ar & ")=2, ""Y"")))"
        .Range("Q" & ar).Formula = "=IF(D" & ar & "="""", """", IF(L" & ar & "=TRUE, ""Requested"", 'Set Up'!K" & ar & "))"
        .Range("R" & ar).Formula = "=IF(D" & ar & "="""", """", IF(Copy!M" & ar & "=""Copy Not Attached"", Copy!I" & ar & ", Copy!M" & ar & "))"
        .Range("S" & ar).Formula = "=Copy!J" & ar
        .Range("T" & ar).Formula = "='PCA & Feedback'!I" & ar
        .Range("U" & ar).Formula = "=IF('PCA & Feedback'!Q" & ar & "=""Y"", 'PCA & Feedback'!R" & ar & ", IF('PCA & Feedback'!N" & ar & "="""", 'PCA & Feedback'!M" & ar & ", 'PCA & Feedback'!N" & ar & "))"
        .Range("V" & ar).Formula = "=IF(COUNTBLANK(Booking!S" & ar & ")+COUNTBLANK('Set Up'!R" & ar & ")+COUNTBLANK(Copy!P" & ar & ")=3, """", Booking!S" & ar & "&""; ""&'Set Up'!R" & ar & "&""; ""&Copy!P" & ar & ")"

    End With

    For Each ws In Worksheets
        ws.Protect , DrawingObjects:=False, AllowFiltering:=True
    Next

    Bo.Activate
    Bo.Range("B" & ar).Activate

    'Application.ScreenUpdating = True

End Sub
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/51858748

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档