Block Error without End If

cdurfey

New Member
Joined
Sep 25, 2017
Messages
3
I keep getting this error when running the following code. Please help. Thanks

Rich (BB code):
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("B1")) Is Nothing Then


Dim myMSG As String


myString = "***Processing Customer Reports***"
mystring2 = "***Complete***"
Application.StatusBar = myString


'Turnoff Monitor updates


Application.ScreenUpdating = False


Application.DisplayAlerts = False


' Run # of Days of reports selected by User
 
If Target.Value = "1" Then
    Sheets("General Information").Select
    Application.Run "PERSONAL.XLSB!Customer_1_Day"
End If
 
If Target.Value = "2" Then
    Sheets("General Information").Select
    Application.Run "PERSONAL.XLSB!Combine2"
End If
 
If Target.Value = "3" Then
    Sheets("General Information").Select
    Application.Run "PERSONAL.XLSB!Combine3"
End If
 
If Target.Value = "4" Then
     Sheets("General Information").Select
     Application.Run "PERSONAL.XLSB!Combine4"
End If
 
If Target.Value = "5" Then
     Sheets("General Information").Select
     Application.Run "PERSONAL.XLSB!Combine5"
 End If
 
If Target.Value = "6" Then
     Sheets("General Information").Select
     Application.Run "PERSONAL.XLSB!Combine6"
End If


If Target.Value = "7" Then
     Sheets("General Information").Select
     Application.Run "PERSONAL.XLSB!Combine7"
End If
 
If Target.Value = "8" Then
     Sheets("General Information").Select
     Application.Run "PERSONAL.XLSB!Combine8"
End If
 
If Target.Value = "9" Then
     Sheets("General Information").Select
     Application.Run "PERSONAL.XLSB!Combine9"
End If


If Target.Value = "10" Then
     Sheets("General Information").Select
     Application.Run "PERSONAL.XLSB!Combine10"
End If
    
Application.ScreenUpdating = True


Application.StatusBar = False
    
End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
You need to add another End If just before the End Sub
 

PatOBrien198

Board Regular
Joined
Sep 13, 2017
Messages
236
Your very first "IF" has no End IF

If Not Intersect(Target, Range("B1")) Is Nothing Then
 

cdurfey

New Member
Joined
Sep 25, 2017
Messages
3
Thank you for the help. Did not see that one.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
For reference, if you were willing to rename
Customer_1_Day macro to
Combine1
You could rewrite you event as follows
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B1")) Is Nothing Then
    
        Dim myMSG As String
        
        myString = "***Processing Customer Reports***"
        mystring2 = "***Complete***"
        Application.StatusBar = myString
        
        
        'Turnoff Monitor updates
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        Sheets("General Information").Select
        Application.Run "PERSONAL.XLSB!combine" & Target.Value
        
        Application.ScreenUpdating = True
        Application.StatusBar = False
    End If
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,089,862
Messages
5,410,856
Members
403,331
Latest member
dignityy

This Week's Hot Topics

Top