Run-Time Error '91': Object variable or With block variable not set

[ceclay]

I have encounter a situation, the codes that i have below sometime runs without any error message, but sometime it gives me a Run-Time Error '91': Object variable or With block variable not set

Sub NFLH2H2()

Application.ScreenUpdating = False

''Total Receiving Yards by the Player

Const FindWhat As String = "Total Receiving Yards by the Player"
Dim mR As Long, Sr As Range, Sf As Range, gAdr As String, wA As Variant

Set Sr = Sheets("NFL H2H Paste Here").Range("A1:A20000")
Set Sf = Sr.Find(FindWhat, [A1], xlFormulas, xlPart, xlByRows, xlNext)


gAdr = Sf.Address
wA = Sf.Resize(8, 1).Value
Sheets("Total Receiving Yards").Range("A1").Resize(8, 1).Value = wA
Do
    Set Sf = Sr.FindNext(Sf)
    If Sf Is Nothing Then GoTo NextI
    If Sf.Address = gAdr Then Exit Do
    wA = Sf.Resize(8, 1).Value
    With Sheets("Total Receiving Yards")
        mR = .Range("A" & Rows.count).End(xlUp).Row + 1
        .Range("A" & mR).Resize(8, 1).Value = wA
    End With
Loop

Dim s As Integer, iRow As Integer
Dim arrSource As Variant

'Set the first row
sRow = 2

With ActiveWorkbook.Worksheets("Total Receiving Yards")
    'get the data into an array from the first column
    arrSource = Range(.Cells(1, 1), .Cells(.Rows.count, 1).End(xlUp))

    'parse every value of the array and add the data to the next column
    For s = 2 To (UBound(arrSource) - UBound(arrSource) Mod 8) Step 8
        .Cells(sRow, 3) = arrSource(s, 1)
        .Cells(sRow, 4) = arrSource(s + 1, 1)
        .Cells(sRow, 5) = arrSource(s + 2, 1)
        .Cells(sRow, 6) = arrSource(s + 3, 1)
        .Cells(sRow, 7) = arrSource(s + 4, 1)
        .Cells(sRow, 8) = arrSource(s + 5, 1)
        .Cells(sRow, 9) = arrSource(s + 6, 1)
        .Cells(sRow, 10) = arrSource(s + 7, 1)

        sRow = sRow + 1
    Next s

End With

NextI:
Worksheets("Total Receiving Yards").Select
Columns("A:K").EntireColumn.Hidden = True

End Sub

 

[JoeMo]

Is the line that generates the error (wA = Sf.Resize(8, 1).Value) the one before the Do loop, or the one within the Do loop?

 

[ceclay]

Before the Do loop
By the way thank you JoeMo for trying to help really appreciate it.

 

[DanteAmor]

This would simplify your macro.

Sub NFLH2H2()
  ''Total Receiving Yards by the Player
  Dim Sr As Range, Sf As Range, cell As String, sRow As Long, Sh As Worksheet
  Application.ScreenUpdating = False
  
  Set Sh = Sheets("Total Receiving Yards")
  Const FindWhat As String = "Total Receiving Yards by the Player"
  sRow = 2
  Set Sr = Sheets("NFL H2H Paste Here").Range("A1:A20000")
  Set Sf = Sr.Find(FindWhat, , xlValues, xlPart)
  If Not Sf Is Nothing Then
    cell = Sf.Address
    Do
      Sh.Range("A" & Rows.Count).End(xlUp)(2).Resize(8, 1).Value = Sf.Resize(8, 1).Value
      Sh.Range("C" & sRow).Resize(1, 8).Value = Application.Transpose(Sf.Resize(8, 1).Value)
      sRow = sRow + 1
      Set Sf = Sr.FindNext(Sf)
    Loop While Not Sf Is Nothing And Sf.Address <> cell
  End If
End Sub

 

[JoeMo]

What's the value of gAdr when you get the error? When you get the error click Debug then, open the Immediate Window (ctrl+g) and type:
?Sf.parent.name
and press Enter. What's returned - is it the name of the sheet you expect Sf to be on?

 

[DanteAmor]

If the "Total Receiving Yards" sheet is clean, then the macro could be simplified in this way:

Sub NFLH2H2()
''Total Receiving Yards by the Player
  Dim Sh As Worksheet, Sr As Range, Sf As Range, cell As String
  Set Sh = Sheets("Total Receiving Yards")
  Set Sr = Sheets("NFL H2H Paste Here").Range("A1:A20000")
  Set Sf = Sr.Find("Total Receiving Yards by the Player", , xlValues, xlPart)
  If Not Sf Is Nothing Then
    cell = Sf.Address
    Do
      Sh.Range("A" & Rows.Count).End(xlUp)(2).Resize(8, 1).Value = Sf.Resize(8, 1).Value
      Sh.Range("C" & Rows.Count).End(xlUp)(2).Resize(1, 8).Value = Application.Transpose(Sf.Resize(8, 1).Value)
      Set Sf = Sr.FindNext(Sf)
    Loop While Not Sf Is Nothing And Sf.Address <> cell
  End If
End Sub

 

[ceclay]

Thank you DanteAmor!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.