vba - 循环工作表时对象定义错误

Question

我正在处理一个项目，该项目需要遍历一系列工作表，每个工作表都以单独工作表中的一系列值命名。然后我在每张纸上执行一些功能，将公式添加到下一个空列。但是，我的代码在这一行出错了:

Worksheets(Name).Range(.Cells(2, LastColumn + 1)).Formula = "=F2"     

具体错误是

"Application-defined or Object-defined error"

而且我不确定为什么会发生这种情况。我已经改变了引用工作表的方式，在 With-blocks 周围移动等。请注意，这只是一个 Sub，我一直在测试完整宏的不同组件。对此错误或我做错的任何帮助将不胜感激!

Sub Test()
    Dim ws2 As Worksheet
    Dim wb As Workbook
    Dim LastRow As Long, LastColumn As Long
    Dim LastRow2 As Long
    Dim Name As Variant, SheetR As Variant

    Set wb = ActiveWorkbook
    Set ws2 = wb.Sheets("Comm")

    LastRow2 = 6

    'sort each sheet on date descending
    With wb
        SheetR = ws2.Range("A3:A" & (LastRow2 + 2))
        For Each Name In SheetR
            LastColumn = 0
            LastRow = 0
            With Worksheets(Name)
                Worksheets(Name).Rows("1:1").AutoFilter
                Worksheets(Name).AutoFilter.Sort.SortFields.Add Key:=Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                With Worksheets(Name).AutoFilter.Sort
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                LastColumn = Worksheets(Name).Cells(1, Columns.Count).End(xlToLeft).Column
                LastRow = Worksheets(Name).Cells(Rows.Count, 1).End(xlUp).Row
                If LastRow = 1 Then
                ElseIf LastRow = 2 Then
                ElseIf LastRow = 3 Then
                ElseIf LastRow = 4 Then
                ElseIf LastRow > 4 Then
                    'The error is occurring at this next line
                    Worksheets(Name).Range(.Cells(2, LastColumn + 1)).Formula = "=F2"
                    Worksheets(Name).Range(.Cells(3, LastColumn + 1)).Formula = "=F3+O2"
                    Worksheets(Name).Range(.Cells(3, LastColumn + 1)).Select
                    Selection.AutoFill Destination:=Sheets(CStr(Name)).Range(.Cells(4, LastColumn + 1), .Cells(LastRow, LastColumn + 1)), Type:=xlFillDefault
                Else
                End If
            End With
        Next Name
    End With

End Sub

Answer 1

看我的注释。

Sub Test()
    Dim ws2 As Worksheet, wb As Workbook, LastRow As Long, LastColumn As Long, LastRow2 As Long, Name As Variant, SheetR As Variant
    Set wb = ActiveWorkbook
    Set ws2 = wb.Sheets("Comm")
    LastRow2 = 6
    'sort each sheet on date descending
    SheetR = ws2.Range("A3:A" & (LastRow2 + 2))
    For Each Name In SheetR
        LastColumn = 0
        LastRow = 0
        With Worksheets(Name)
            .Rows("1:1").AutoFilter
            .AutoFilter.Sort.SortFields.Add Key:=.Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal  'Added "." before the Key range
            With .AutoFilter.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column 'Added "." before Columns.Count
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row 'Added "." before Rows.Count
            If LastRow = 1 Then
            ElseIf LastRow = 2 Then
            ElseIf LastRow = 3 Then
            ElseIf LastRow = 4 Then
            ElseIf LastRow > 4 Then
                'The error is occurring at this next line
            .Cells(2, LastColumn + 1).Formula = "=F2"  'Removed .range() as this is only a single cell being called
            .Cells(3, LastColumn + 1)).Formula = "=F3+O2"  'Removed .range() as this is only a single cell being called
            .Cells(3, LastColumn + 1)).Select  'Removed .range() as this is only a single cell being called
            Selection.AutoFill Destination:=Sheets(CStr(Name)).Range(.Cells(4, LastColumn + 1), .Cells(LastRow, LastColumn + 1)), Type:=xlFillDefault 'Need to check your qualifiers in this line... using source, not destination
            Else
            End If
        End With
    Next Name
End Sub

Edit1:修复了在单个单元格上对 range() 的不当调用。向 u/PeterT 表示支持