当运行代码时，它返回一个范围类的-Autofill方法，该方法在代码的最后一行失败:VBA

Question

我是VBA的新手，很难理解代码的下落。我收到消息"Autofill method of Range class failed"。请帮我解决

代码：

Sub SomeName()
Dim lRow As Long
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wkSheet As Worksheet
Set wkSheet = wb.ActiveSheet

lRow = wkSheet.Range("G" & wkSheet.Rows.Count).End(xlUp).Row

Sheets("MilestoneStatus").Select

lRow = wkSheet.Range("G" & wkSheet.Rows.Count).End(xlUp).Row

wkSheet.Range("H1").Formula = "=IF(ISBLANK(RC[-1]),"""",IF(DAYS(TODAY(),RC[-1])<0,CONCATENATE(""Due in "",-DAYS(TODAY(),RC[-1]),""DAYS""),IF(DAYS(TODAY(),RC[-1])>0,CONCATENATE(""OVERDUE"",DAYS(TODAY(),RC[-1]),""DAYS""),""Due today"")))"

With wkSheet.Range("H1")
.Formula = Range("H1").Formula
.AutoFill Destination:=Range.Offset(0, 0).Resize(lRow)
End With
End Sub

Answer 1

您不需要太多的操作和变量来获得所需的结果，包括.AutoFill。请尝试以下代码：

Sub SomeName()
Dim lRow As Long

With ThisWorkbook.Sheets("MilestoneStatus")
lRow = .Range("G" & .Rows.Count).End(xlUp).Row
.Range("H1").Resize(lRow).FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",IF(DAYS(TODAY(),RC[-1])<0,CONCATENATE(""Due in "",-DAYS(TODAY(),RC[-1]),""DAYS""),IF(DAYS(TODAY(),RC[-1])>0,CONCATENATE(""OVERDUE"",DAYS(TODAY(),RC[-1]),""DAYS""),""Due today"")))"
End With
End Sub

如果出于某种原因仍然需要使用.AutoFill，可以这样做：

Sub SomeName()
Dim lRow As Long

With ThisWorkbook.Sheets("MilestoneStatus")
lRow = .Range("G" & .Rows.Count).End(xlUp).Row
.Range("H1").FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",IF(DAYS(TODAY(),RC[-1])<0,CONCATENATE(""Due in "",-DAYS(TODAY(),RC[-1]),""DAYS""),IF(DAYS(TODAY(),RC[-1])>0,CONCATENATE(""OVERDUE"",DAYS(TODAY(),RC[-1]),""DAYS""),""Due today"")))"
.Range("H1").AutoFill Destination:=.Range("H1").Resize(lRow)
End With
End Sub

我还建议您注意如何避免在Excel VBA 中使用Select