VBA ERROR

[Hamed parhizkar]

I have this function in VBA but I get an error everytime:

When I try to run it I get an error saying "Block If without End if"

My very last function ends with:

End If

End Sub

But if I take the end sub out I get an error that reads" Expected end Sub"

???

Private Sub CommandButton1_Click()

If Range("AB54") > 0 Then
Range("Z48:AC59").Select
ActiveSheet.PageSetup.PrintArea = "$Z$48:$AC$59"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

If Range("AB65") > 0 Then
Range("Z60:AC71").Select
ActiveSheet.PageSetup.PrintArea = "$Z$60:$AC$71"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

If Range("AB80") > 0 Then
Range("Z73:AC86").Select
ActiveSheet.PageSetup.PrintArea = "$Z$73:$AC$86"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

End Sub

 

[Jason Lepack]

Private Sub CommandButton1_Click()

If Range("AB54") > 0 Then
Range("Z48:AC59").Select
ActiveSheet.PageSetup.PrintArea = "$Z$48:$AC$59"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Missing end if here.

If Range("AB65") > 0 Then
Range("Z60:AC71").Select
ActiveSheet.PageSetup.PrintArea = "$Z$60:$AC$71"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Missing end if here.

If Range("AB80") > 0 Then
Range("Z73:AC86").Select
ActiveSheet.PageSetup.PrintArea = "$Z$73:$AC$86"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

End Sub

I have noted where you are missing the end ifs. Unless of course you
mean to use Elseif (see below)

Private Sub CommandButton1_Click()

If Range("AB54") > 0 Then
Range("Z48:AC59").Select
ActiveSheet.PageSetup.PrintArea = "$Z$48:$AC$59"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ElseIf Range("AB65") > 0 Then
Range("Z60:AC71").Select
ActiveSheet.PageSetup.PrintArea = "$Z$60:$AC$71"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ElseIf Range("AB80") > 0 Then
Range("Z73:AC86").Select
ActiveSheet.PageSetup.PrintArea = "$Z$73:$AC$86"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

End Sub

Cheers,
Jason Lepack

 

[Rick Rothstein \(MVP - VB\)]

Every If-Then grouping needs its own End If. However, there is also a
question of intent that needs to be answered. Is the intent for each of
those If-Then groupings to run independently of the others? If so, then your
code should look like this....

Private Sub CommandButton1_Click()
If Range("AB54") > 0 Then
Range("Z48:AC59").Select
ActiveSheet.PageSetup.PrintArea = "$Z$48:$AC$59"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

If Range("AB65") > 0 Then
Range("Z60:AC71").Select
ActiveSheet.PageSetup.PrintArea = "$Z$60:$AC$71"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

If Range("AB80") > 0 Then
Range("Z73:AC86").Select
ActiveSheet.PageSetup.PrintArea = "$Z$73:$AC$86"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
End Sub

However, if the intent is for the 2nd If-Then grouping to run ONLY if the
first If-Then statement is True and the 3rd If-Then grouping to run ONLY if
the 2nd one is True, then the code would look like this instead...

Private Sub CommandButton1_Click()
If Range("AB54") > 0 Then
Range("Z48:AC59").Select
ActiveSheet.PageSetup.PrintArea = "$Z$48:$AC$59"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

If Range("AB65") > 0 Then
Range("Z60:AC71").Select
ActiveSheet.PageSetup.PrintArea = "$Z$60:$AC$71"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

If Range("AB80") > 0 Then
Range("Z73:AC86").Select
ActiveSheet.PageSetup.PrintArea = "$Z$73:$AC$86"
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
End If

End If

End If
End Sub

Notice the placement of the End If statements in each example. The placement
of those statements governs the code that will be executed and when. They
are very important and I think you will find proper indenting of nested
blocks will make placing them easier.

Rick