VB错误"Object variable or With block variable not set (Error 91)"

Question

    Option Explicit
    Private Sub Worksheet_Activate()
    Dim r As Range, rng As Range, snRow As Range, TmRow As Range
    Dim x As Integer, ETRow As Long, LTRow As Long
    Dim TMName As String
    Application.ScreenUpdating = False
    ETRow = 10: LTRow = 10
    ActiveSheet.Range("C4:AG5,C11:L41").ClearContents
    For x = 1 To Sheets.Count
    If Sheets(x).Name <> "Summary" Then
        With Sheets(Sheets(x).Name)
            TMName = Left(Sheets(x).Name, 6)
            With .Range("C:C")
             Set snRow = .Find("Total Staff (inc Supervisors)", LookIn:=xlValues, LookAt:=xlWhole)
            End With
            Set rng = .Range("D5", "AH5")
            For Each r In rng
                If InStr(1, r.Value, "LT") > 0 Then
                    With Sheets("Summary")
                        .Cells(5, r.Column - 1) = Sheets(Sheets(x).Name).Cells(snRow.Row, r.Column).Value
                        With .Range("I9:L9")
                            Set TmRow = .Find(TMName, LookIn:=xlValues, LookAt:=xlWhole)
                        End With
   .Cells(LTRow, TmRow.Column) = Left(r.Value, InStr(1, r.Value, "LT", vbTextCompare) - 1)
                        LTRow = LTRow + 1
                    End With
                ElseIf InStr(1, r.Value, "ET") > 0 Then
                    With Sheets("Summary")
                        .Cells(4, r.Column - 1) = Sheets(Sheets(x).Name).Cells(snRow.Row, r.Column).Value
                        With .Range("C9:F9")
                            Set TmRow = .Find(TMName, LookIn:=xlValues, LookAt:=xlWhole)
                        End With
                        .Cells(ETRow, TmRow.Column) = Left(r.Value, InStr(1, r.Value, "ET", vbTextCompare) - 1)
                        ETRow = ETRow + 1
                    End With
                End If
            Next
        End With
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

说

有一个问题

.Cells(LTRow, TmRow.Column) = Left(r.Value, InStr(1, r.Value, "LT", vbTextCompare) - 1)

和

.Cells(ETRow, TmRow.Column) = Left(r.Value, InStr(1, r.Value, "ET", vbTextCompare) - 1)

如果用户在日期旁放入ET或LT，则该代码在带有4张的花名册上工作，然后计算某人值班（由W）

表示

代码用于摘要表。

不确定为什么它不起作用，但是一旦我尝试通过在C5下方添加额外的行来更改实际摘要表，就会发生这种情况。然后，即使我撤消了一切，它仍然发生。

Answer 1

问题是您将值分配给 .Cells(LTRow, TmRow.Column)和在您拥有之前的行中：

Set TmRow = .Find(TMName, LookIn:=xlValues, LookAt:=xlWhole)

因此，如果TmRow未通过.Find()分配给值，则TmRow.Column将会给出此错误。

尝试这样四处走动：

With .Range("I9:L9")
    Set TmRow = .Find(TMName, LookIn:=xlValues, LookAt:=xlWhole)
    If TmRow Is Nothing Then
        MsgBox "TmRow knows nothing"
        Stop
    End If
End With

然后考虑一种重建代码的方法。