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]

What line causes the error?

 

[ceclay]

gAdr = Sf.Address

 

[JoeMo]

First, change this line:
Set Sf = Sr.Find(FindWhat, [A1], xlFormulas, xlPart, xlByRows, xlNext)

to this:
Set Sf = Sr.Cells.Find(FindWhat, xlFormulas, xlPart, xlByRows, xlNext)

then replace the offending line with this block:

If Not Sf is Nothing then
gAdr = Sf.Address
Else
Msgbox FindWhat & " not found on sheet" & Sr.name
End If

 

[ceclay]

Hi, After inputting the line above another error 91 : Object variable or With block variable not set arise on line

wA = Sf.Resize(8, 1).Value

 

[JoeMo]

Does the block you inserted confirm that Sf is NOT Nothing? In other words, did you get a message box indicating FindWhat could not be found?

 

[ceclay]

No. Im getting Run Time error 91 after inserting the block above. And when I debug Run Time error 91 on line
wA = Sf.Resize(8, 1).Value

 

[JoeMo]

That's not what I'm asking. Does this block that comes before the wA = ... line,

If Not Sf is Nothing then
gAdr = Sf.Address
Else
Msgbox FindWhat & " not found on sheet" & Sr.name
End If

generate a message box saying FindWhat cannot be found? If not, can you post the modified code that's producing the current error?

 

[DataBlake]

if sf = nothing then i get the same error
if i change
Set Sf = Sheets(1).Range("A1")
the error goes away

its your data that generates the error by not having any "Total Receiving Yards by the Player" in A1:A20000

 

[ceclay]

It is not generating message box saying FindWhat cannot be found. Below is the modified code

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.Cells.Find(FindWhat, xlFormulas, xlPart, xlByRows, xlNext)


If Not Sf Is Nothing Then
gAdr = Sf.Address
Else
MsgBox FindWhat & " not found on sheet" & Sr.Name
End If

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