Debug Error (91) - If Statement Inside a Loop

[larinda4]

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?

 

[jasonb75]

You've declared wbBook as a workbook type variable but you haven't set a workbook to the variable, it is currently empty.

 

[larinda4]

You've declared wbBook as a workbook type variable but you haven't set a workbook to the variable, it is currently empty.

Excuse my ignorance but what do you mean? Would I use the name of my workbook for that?

 

[jasonb75]

When you declare an object (for example a workbook) as a variable by using Dim wbBook As WorkBook you are only telling excel that wbBook will refer to a workbook, you also have to tell it which workbook it refers to (you need to do the same for worksheets too).

Taking the first part of your code as an example, you would need to do something like this. You can either set it by name, which is probably the easiest way to learn but there are other ways to identify a workbook. I've used ThisWorkbook to identify it below which identifies the workbook that the code is saved to.

Dim wbBook As Workbook
Dim wsSheet As Worksheet

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.WorkSheets("Master")

If wsSheet.Range("P69").Value = 1 Then
    wsSheet.Rows("68:70").EntireRow.Hidden = True
End If

 

[larinda4]

When you declare an object (for example a workbook) as a variable by using Dim wbBook As WorkBook you are only telling excel that wbBook will refer to a workbook, you also have to tell it which workbook it refers to (you need to do the same for worksheets too).

Taking the first part of your code as an example, you would need to do something like this. You can either set it by name, which is probably the easiest way to learn but there are other ways to identify a workbook. I've used ThisWorkbook to identify it below which identifies the workbook that the code is saved to.

Dim wbBook As Workbook
Dim wsSheet As Worksheet

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.WorkSheets("Master")

If wsSheet.Range("P69").Value = 1 Then
    wsSheet.Rows("68:70").EntireRow.Hidden = True
End If

Thanks Jason!

When I updated the code to what you suggested, I'm receiving a "Compile error: Argument not optional" at the Sub PrintbyDiv() section now.

 

[jasonb75]

Have you made any other changes? I can see nothing in your code that should prompt that error.

 

[larinda4]

Have you made any other changes? I can see nothing in your code that should prompt that error.

No, you're right. I accidentally moved something.

Thank you so much for your help! No more run time errors and it's working perfectly!

You're a life saver, Jason. I am truly, very appreciative. Thank you again!