Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ERROR
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ERROR
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ERROR
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 "Hamed parhizkar" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |