none
Compile Error, Block If without End If. Highlight the End Sub command line RRS feed

  • Question

  • Hi Guys - not sure i have put this in the correct forum but we shall give it a go

    I am working on a master excel spreadsheet that identifies its own file path, searches for a folder called EO_121DATA in that file path and then takes a specific sheet (121 Data) from each workbook in the folder and create a copy of it in the master, labelling it the name of person which is in cell B2.

    It will be updated every month and it needs to remove sheets when files are deleted and add sheets when new ones are created.

    I think I have written something that will work, and once I know the script is fine I will create a button to initiate it. 

    When I run the script I get the error: Compile Error Block If without End If and it highlights the End Sub command line at the bottom.

    Here's what I've written so far, when its in use it will be running on Excel 2010, though I am currently editing it at home on 2007.

    Sub Import_Team()
    
    Dim wsSrc As Worksheet                                              ' Source Sheet
    Dim wbSrc As Workbook                                               ' Source Workbook
    Dim wbDst As Workbook                                               ' Destination Workbook
    Dim shDst As Worksheet                                              ' Destination Worksheet
    Dim useWS As Boolean                                                ' Used to indicate Start and End Sheets
    
    Dim ManagerPath As String                                           ' Find manager workbook file path
    Dim OfficerPath As String                                           ' Find folder EO_121DATA in team folder for the manager
    Dim srcFilename As String                                           ' Finds workbooks in EO_121Data
    
                                                                        'define the file paths
    ManagerPath = Application.ThisWorkbook.Path                         ' Finds the filepath of the workbook running the macro
    OfficerPath = Dir(ManagerPath & "\EO_121Data")                      ' Adds the EO_121DATA to the above filepath, as long as the manager file is kept in the folder that contains EO_121DATA it will find the relevent files
    srcFilename = Dir(OfficerPath & "\*.xls*", vbNormal)                ' Finds any excel workbooks in the EO_121DATA file, the asterisk are wildcards so any excel format and sheet name will be found
    
    useWS = False
    
    If OfficerPath = "" Then
    MsgBox "There is currently no folder containing Enquiry Officer documents. Please create a file called EO_121DATA in the same file as your manager sheet and copy your teams files into it.", vbOKOnly, "No Destination File"
    Exit Sub                                                            ' If there is no EO_121DATA folder the manager will be told and the sub will stop.
    
    Do Until OfficerPath <> ""
    
    If srcFilename = "" Then
    MsgBox "There are currently no team data sheets saved in the folder EO_121DATA.", vbOKOnly, "No Data Available"
    Exit Sub                                                            ' If there are no files in EO_121DATA it will stop the macro and do nothing, a message box advising no files were found in the specified folder will show.
    
    Do Until srcFilename <> ""
                                                                        ' Stop the screen from showing each workbook being opened checked and closed
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For Each shDst In ActiveWorkbook.Sheets                             ' Clears old worksheets in the book
    
    If shDst.Name = "End" Then
    useWS = False                                                       ' Turns off sheet processing
    End If
    
    If shDst.Name = "Start" Then
    useWS = True                                                        ' Turns on sheet processing
    End If
    
    If useWS = True And shDst.Name <> "Start" Then
    shDst.Delete                                                        ' Deletes old sheets ready to be updated, run the macro each month as officer sheets are updated
    End If
    
    Next shDst
    
                                                                        ' Loop through the source files in EO_121DATA
    While srcFilename <> ""
    TabName = Replace(srcFilename, "Tracker.xlsm", "")                  ' Creates a tab
    Worksheets.Add Before:=Sheets("End")
    ActiveSheet.Name = TabName
    Set shDst = ActiveSheet
    
                                                                        ' Copy from source workbook to destination workbook
    
    Set wbSrc = Workbooks.Open(srcFilename)
    Set shSrc = wbSrc.Sheets("121 Data")
    shDst.Name = shSrc.Range("$B$2")
    shSrc.Cells.Copy
    shDst.Range("A1").PasteSpecial xlPasteValues
    shDst.Columns.AutoFit
    wbSrc.Close savechanges:=False
    
                                                                        ' Get the next file
    srcFilename = Dir()
    
    Wend
    Loop
    
    Sheets("Team Average").Select
                                                                        ' re-enables the screen so it shows files being opened and closed
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    End Sub
    
    
    

    Thanks in advance for your help.

    Thursday, June 15, 2017 7:59 PM

All replies

  • Hi,

    there's no correspondinmg "End If" for the lines: (the first two if statements)

    If OfficerPath = "" Then

    and

        If srcFilename = "" Then

    Regards,

      Thorsten

    Thursday, June 15, 2017 8:22 PM
  • If OfficerPath = "" Then
    MsgBox
    "There is currently no folder containing Enquiry Officer documents. Please create a file called EO_121DATA in the same file as your manager sheet and copy your teams files into it.", vbOKOnly, "No Destination File"
    Exit Sub      

    If something then

       do something

    end if

    Where is the "end if" that you are using on other if statements?                                                     

    Thursday, June 15, 2017 8:23 PM
  • Hi ATaylor028,

    In addition to Thorsten Gudera's opinion, please note that there are two Do Until and one While in your code, but I could not see End while and only one Loop.

      Do {Until} condition
        statements
        [Exit Do]
        statements
    Loop
    While Not foo
            Debug.WriteLine("!")
        End While

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, June 16, 2017 6:36 AM
  • No this is not the right forum for it. 

    Your code is Visual Basic for Application. This forum is for VB for .Net on which your code won't run. 

    But there is a forum for that in the Visual Studio Range

    https://social.msdn.microsoft.com/Forums/en-us/home?forum=isvvba


    Success
    Cor

    Friday, June 16, 2017 7:44 AM
  • Hi ATaylor028,

    I try to test your code on my side.

    I find that there are some loop ending and if condition ending statements are missing.

    I try to correct it.

    below is the corrected code.

    Option Explicit
    
    Sub Import_Team()
    
    Dim wsSrc As Worksheet                                              ' Source Sheet
    Dim wbSrc As Workbook                                               ' Source Workbook
    Dim wbDst As Workbook                                               ' Destination Workbook
    Dim shDst As Worksheet                                              ' Destination Worksheet
    Dim useWS As Boolean                                                ' Used to indicate Start and End Sheets
    
    Dim ManagerPath As String                                           ' Find manager workbook file path
    Dim OfficerPath As String                                           ' Find folder EO_121DATA in team folder for the manager
    Dim srcFilename, TabName As String                                          ' Finds workbooks in EO_121Data
    Dim shSrc As Object
                                                                        'define the file paths
    ManagerPath = Application.ThisWorkbook.Path                         ' Finds the filepath of the workbook running the macro
    OfficerPath = ManagerPath & "\EO_121Data"                      ' Adds the EO_121DATA to the above filepath, as long as the manager file is kept in the folder that contains EO_121DATA it will find the relevent files
    srcFilename = Dir(OfficerPath & "\*.xls*", vbNormal)                ' Finds any excel workbooks in the EO_121DATA file, the asterisk are wildcards so any excel format and sheet name will be found
    Debug.Print ManagerPath
    Debug.Print OfficerPath
    useWS = False
    
        If OfficerPath = "" Then
        MsgBox "There is currently no folder containing Enquiry Officer documents. Please create a file called EO_121DATA in the same file as your manager sheet and copy your teams files into it.", vbOKOnly, "No Destination File"
        Exit Sub                                                            ' If there is no EO_121DATA folder the manager will be told and the sub will stop.
        End If
        
            Do Until OfficerPath <> ""
            
            If srcFilename = "" Then
            MsgBox "There are currently no team data sheets saved in the folder EO_121DATA.", vbOKOnly, "No Data Available"
            Exit Sub                                                            ' If there are no files in EO_121DATA it will stop the macro and do nothing, a message box advising no files were found in the specified folder will show.
            End If
            Loop
            
                Do Until srcFilename <> ""
                                                                                    ' Stop the screen from showing each workbook being opened checked and closed
                    Application.DisplayAlerts = False
                    Application.EnableEvents = False
                    Application.ScreenUpdating = False
                
                        For Each shDst In ActiveWorkbook.Sheets                             ' Clears old worksheets in the book
                            
                            If shDst.Name = "End" Then
                            useWS = False                                                       ' Turns off sheet processing
                            End If
                            
                            If shDst.Name = "Start" Then
                            useWS = True                                                        ' Turns on sheet processing
                            End If
                            
                            If useWS = True And shDst.Name <> "Start" Then
                            shDst.Delete                                                        ' Deletes old sheets ready to be updated, run the macro each month as officer sheets are updated
                            End If
                        
                        Next shDst
                        
                                                                                            ' Loop through the source files in EO_121DATA
                        While srcFilename <> ""
                                TabName = Replace(srcFilename, "Tracker.xlsm", "")                  ' Creates a tab
                                Worksheets.Add Before:=Sheets("End")
                                ActiveSheet.Name = TabName
                                Set shDst = ActiveSheet
                                
                                                                                                    ' Copy from source workbook to destination workbook
                                
                                Set wbSrc = Workbooks.Open(srcFilename)
                                Set shSrc = wbSrc.Sheets("121 Data")
                                shDst.Name = shSrc.Range("$B$2")
                                shSrc.Cells.Copy
                                shDst.Range("A1").PasteSpecial xlPasteValues
                                shDst.Columns.AutoFit
                                wbSrc.Close savechanges:=False
                                
                                                                                                    ' Get the next file
                                srcFilename = Dir()
                                
                                Wend
                Loop
    
    Sheets("Team Average").Select
                                                                        ' re-enables the screen so it shows files being opened and closed
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    End Sub
    
    

    I find that the path values are not get stored correctly in the variable for the path. so I try to correct it.

    you can try to test the code on your side.

    I find that, at the end if, path variables are not null then you are just selecting the sheet.

    but note that if variables are not empty then it will not go inside the loop.

    if it not go inside the loop then it will not open the workbook.

    if workbook is not open and you try to select the sheet then you will get an error.

    so at this place your logic is wrong.

    you need to first clear the idea what you want to do when this code get run.

    then you need to think the correct flow of the code.

    then try to develop a code. you need to take care about if file names are available then what it should do and if file names are not available then what should it do.

    try to debug the code to get the accurate flow without any error.

    try to follow the suggestion and try to modify the code.

    if you get stuck anywhere or you get any error then let me know about that.

    I will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 19, 2017 1:02 AM
    Moderator
  • Hi ATaylor028,

    I find that since you had created this thread, you did not visit this thread again.

    is your issue is solved now?

    if yes then please try to post the solution and mark it as an answer.

    if your issue is still exist then try to refer the suggestion provided by the community members.

    if then also you have issue then let us know about that we will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 23, 2017 7:37 AM
    Moderator