If Statement inside a Loop keeps Crashing

Question

Below is my code and I'm having a difficult time figuring out where I went wrong or where I'm missing a statement.

I'll describe what I need it to do first and maybe that will help figuring it out.

On a separate tab (we'll call it List) it contains a list of numbers in column A and the location it should be saved (PDF'd) in column E.

I want it to pull the first number from column A into another tab called Master that already has formulas that will generate information that I need. However, I want to hide rows that are not applicable to this specific number before it PDF's it to the location from Column E in the List tab.

I'll need it to hide rows based on a value of 1 (again, I used formulas in the Master tab) and then unhide the rows after it PDF's it. Grab the next number from the List tab, hide applicable rows, PDF it, then unhide the rows, rinse and repeat.

Here's my code:

Sub PrintbyDiv()

I = 189

Do While I < 197

    Sheets("List").Select
    Cells(I, 1).Select
    Selection.Copy
        Sheets("Master").Select
    Range("B8").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
      
Dim wbBook As Workbook
Dim wsSheet As Worksheet

    If wbBook.Worksheets("Master").Range("P69").Value = 1 Then
    wbBook.Worksheets("Master").Rows("68:70").EntireRow.Hidden = True
    End If
    If wbBook.Worksheets("Master").Range("P72").Value = 1 Then
    wbBook.Worksheets("Master").Rows("71:73").EntireRow.Hidden = True
    End If
    If wbBook.Worksheets("Master").Range("P72").Value = 1 Then
    wbBook.Worksheets("Master").Rows("71:73").EntireRow.Hidden = True
    End If
    If wbBook.Worksheets("Master").Range("P75").Value = 1 Then
    wbBook.Worksheets("Master").Rows("74:76").EntireRow.Hidden = True
    End If
    If wbBook.Worksheets("Master").Range("P77").Value = 1 Then
    wbBook.Worksheets("Master").Rows("77:80").EntireRow.Hidden = True
    End If
    If wbBook.Worksheets("Master").Range("P81").Value = 1 Then
    wbBook.Worksheets("Master").Rows("81:83").EntireRow.Hidden = True
    End If
    If wbBook.Worksheets("Master").Range("P84").Value = 1 Then
    wbBook.Worksheets("Master").Rows("84:86").EntireRow.Hidden = True
    End If
        
        
    Sheets("PrintQ").Select
    ThisFile = Cells(I, 5).Value
    Sheets("Master").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisFile, Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False, OpenAfterPublish:= _
    False

    
I = I + 1

Loop

End Sub

The DEBUG prompt happens as soon as it hits my first if statement to hide rows. It says "Run-time Error '91': Object variable or With block variable not set". What am I doing wrong?

I assume you get a runtime error, not that Excel is crashing? What is the exact error message? Have you assigned anything to `wbBook` (your code doesn't show so)? — FunThomas, Nov 15 '21 at 17:00
I get a run-time error '91': Object variable or With block variable not set — Larinda4, Nov 15 '21 at 17:07
Then most probably you have not assigned anything to `wbBook`. — FunThomas, Nov 15 '21 at 17:09
Please read on [how to avoid using select](https://stackoverflow.com/a/10717999/15597936), selecting sheets/cells is a bad practice in general and should be avoided. If the worksheet `Master` and `List` is on the workbook that runs the macro then you can refer to the workbook by `ThisWorkbook` e.g. `ThisWorkbook.Worksheets("Master").Range("A1") ...` — Raymond Wu, Nov 15 '21 at 17:20

score 0 · Answer 1 · answered Nov 15 '21 at 17:09

Did you define variable I previously, perhaps as a global variable?
Are you sure the workbook you selected is the one you think you selected?

Not a solution, but I see a divide & conquer approach to debugging:

Comment out all the details and leave only the shell, i.e. the Sub() and loop statements. Does that run without error?
Now only comment out the If statements. Does it work? If no, then it's probably not the If statements.
Now uncomment exactly one of the If statements. Does it work?

The issue is that `wbBook` is not `Set`. – BigBen Nov 15 '21 at 17:10 — BigBen, Nov 15 '21 at 17:10

If Statement inside a Loop keeps Crashing

1 Answers1