title: VBA Runtime Error 91 - Code Issue?
body: <p>This has most definitely been asked before but I just want to know what is wrong with my code, as it was working previously and now it has suddenly thrown up a Runtime error 91</p>
  
  <pre><code>          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,C10:L38").ClearContents
            For x = 1 To Sheets.Count
            If Sheets(x).Name &lt;&gt; "Summary" Then
            With Sheets(Sheets(x).Name)
            TMName = Left(Sheets(x).Name, 6)
            With .Range("C:C")
            Set snRow = .Find("Total Staff", LookIn:=xlValues,LookAt:=xlWhole)
            End With
            Set rng = .Range("D5", "AH5")
            For Each r In rng
              If InStr(1, r.Value, "LT") &gt; 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
      *************ERROR HERE---&gt; .Cells(ETRow, TmRow.Column) = Left(r.Value, InStr(1, r.Value, "ET", vbTextCompare) - 1)
                      ETRow = ETRow + 1
                  End With
                      ElseIf InStr(1, r.Value, "LT") &gt; 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
  
              End If
          Next
      End With
  End If
     Next
     Application.ScreenUpdating = True
     End Sub
  </code></pre>
  
  <p>Thank you in advance </p>