excel - 运行时错误 '438' : Object does't support this property or method

Question

跟随宏几乎可以完美地工作到一个特定的行。

Sub Top15()

Dim Top15 As Worksheet

Dim lastROW As Long
Dim last15ROW As Long

Dim rangeC As Range
Dim rangeH As Range
Dim rangeI As Range
Dim rangeJ As Range
Dim rangeK As Range
Dim rangeL As Range
Dim rangeM As Range
Dim rangeN As Range
Dim pasteRange As Range

Set Top15 = ThisWorkbook.Sheets("03")

lastROW = Top15.Range("C" & Top15.Rows.Count).End(xlUp).Row + 1
last15ROW = Top15.Range("C" & Top15.Rows.Count).End(xlUp).Row + 16

Set rangeC = Top15.Range("C" & lastROW & ":C" & last15ROW)
Set rangeH = Top15.Range("H" & lastROW & ":H" & last15ROW)
Set rangeI = Top15.Range("I" & lastROW & ":I" & last15ROW)
Set rangeJ = Top15.Range("J" & lastROW & ":J" & last15ROW)
Set rangeK = Top15.Range("K" & lastROW & ":K" & last15ROW)
Set rangeL = Top15.Range("L" & lastROW & ":L" & last15ROW)
Set rangeM = Top15.Range("M" & lastROW & ":M" & last15ROW)
Set rangeN = Top15.Range("N" & lastROW & ":N" & last15ROW)

With Top15
    rangeC.Formula = "=TEXT(WEEKNUM(TODAY()),""0#"")" 'CW
    rangeH.Formula = "=IF(ISBLANK(INDIRECT(""D""&ROW())),"""",INDIRECT(""D""&ROW())&""/""&TEXT(INDIRECT(""C""&ROW()),""0#""))" 'REF
    rangeI.Formula = "=IFERROR(IF(RIGHT(VLOOKUP(INDIRECT(""D""&ROW())&""/""&TEXT(INDIRECT(""C""&ROW())-1,""0#""),H:H,1,FALSE),2)+1=INDIRECT(""C""&ROW()),INDIRECT(""C""&ROW()),1),1)" 'open weeks
    rangeJ.Forumla = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,3,FALSE),"""")" 'Issue description
    rangeK.Forumla = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,4,FALSE),"""")" 'action
    rangeL.Formula = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,5,FALSE),"""")" 'reason
    rangeM.Formula = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,6,FALSE),"""")" 'missing component
    rangeN.Formula = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,7,FALSE),"""")" 'expected D CW

Set pasteRange = .Range(rangeC.Address & ":" & rangeN.Address)

pasteRange.Copy
pasteRange.PasteSpecial xlPasteValues

End With

End Sub

前三个函数有效，但第三个函数出现运行时错误:

With Top15
    rangeC.Formula = "=TEXT(WEEKNUM(TODAY()),""0#"")" 'CW
    rangeH.Formula = "=IF(ISBLANK(INDIRECT(""D""&ROW())),"""",INDIRECT(""D""&ROW())&""/""&TEXT(INDIRECT(""C""&ROW()),""0#""))" 'REF
    rangeI.Formula = "=IFERROR(IF(RIGHT(VLOOKUP(INDIRECT(""D""&ROW())&""/""&TEXT(INDIRECT(""C""&ROW())-1,""0#""),H:H,1,FALSE),2)+1=INDIRECT(""C""&ROW()),INDIRECT(""C""&ROW()),1),1)" 'open weeks
    rangeJ.Forumla = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,3,FALSE),"""")" 'Issue description
    rangeK.Forumla = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,4,FALSE),"""")" 'action
    rangeL.Formula = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,5,FALSE),"""")" 'reason
    rangeM.Formula = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,6,FALSE),"""")" 'missing component
    rangeN.Formula = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,7,FALSE),"""")" 'expected D CW

这是给我运行时错误的公式->

rangeJ.Forumla = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,3,FALSE),"""")" 'Issue description

任何人都知道为什么 VBA 对那个公式有问题吗？

Answer 1

你拼错了范围。公式

您的线路:
rangeJ.Forumla =

应该:
rangeJ.公式 =

发生两次:

rangeJ.Forumla = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,3,FALSE),"""")" 'Issue description
rangeK.Forumla = "=IFERROR(VLOOKUP((INDIRECT(""D""&ROW())&""/""&TEXT((INDIRECT(""C""&ROW())-1),""0#"")),$H:$N,4,FALSE),"""")" 'action

如果您同意，请将答案标记为正确。

希望能帮助到你!