Excel VBA出现"End If without block If"错误求助

Hey there! Let's work through that frustrating "End If without block If" error you're facing, while building the code to find and open your ROI_3 files across two folders. This error almost always boils down to mismatched If statement structure—either mixing single-line and multi-line If syntax, or forgetting to close a block properly.

Common Causes of the Error #

• You wrote a multi-line If statement but didn't include Then on the same line as If [condition], which tells VBA you're starting a block that needs an End If.
• You've got nested If statements where one End If is missing, or the closing statements are out of order.
• Accidentally split a single-line If (which doesn't need End If) into multiple lines without converting it to a block structure.

Working Code for Your Needs #

Here's a clean, error-free implementation that handles opening the files you need, with proper If block structure:

Sub OpenROIFiles()
    Dim controlFolderPath As String, treatedFolderPath As String
    Dim controlFileName As String, treatedFileName As String
    Dim controlWorkbook As Workbook, treatedWorkbook As Workbook
    
    ' Replace these with your actual folder paths
    controlFolderPath = "C:\Your\Actual\Path\Controlfolder\"
    treatedFolderPath = "C:\Your\Actual\Path\Treatedfolder\"
    
    ' Ensure folder paths end with a backslash to avoid file path errors
    If Right(controlFolderPath, 1) <> "\" Then
        controlFolderPath = controlFolderPath & "\"
    End If
    
    If Right(treatedFolderPath, 1) <> "\" Then
        treatedFolderPath = treatedFolderPath & "\"
    End If
    
    ' Find and open file with "ROI_3" in Controlfolder
    controlFileName = Dir(controlFolderPath & "*ROI_3*")
    If controlFileName <> "" Then
        Set controlWorkbook = Workbooks.Open(controlFolderPath & controlFileName)
        Debug.Print "Opened control file: " & controlFileName ' Check Immediate Window for confirmation
    Else
        MsgBox "No file containing 'ROI_3' found in Controlfolder!", vbExclamation
    End If
    
    ' Find and open file with "ROI_3" in Treatedfolder
    treatedFileName = Dir(treatedFolderPath & "*ROI_3*")
    If treatedFileName <> "" Then
        Set treatedWorkbook = Workbooks.Open(treatedFolderPath & treatedFileName)
        Debug.Print "Opened treated file: " & treatedFileName
    Else
        MsgBox "No file containing 'ROI_3' found in Treatedfolder!", vbExclamation
    End If
    
    ' --- Add your calculation logic here ---
    ' Example: Reference cells from both workbooks
    ' controlWorkbook.Sheets("Sheet1").Range("B2").Value = treatedWorkbook.Sheets("Sheet1").Range("B2").Value * 1.5
    
    ' Optional: Close workbooks when done (adjust SaveChanges as needed)
    ' controlWorkbook.Close SaveChanges:=True
    ' treatedWorkbook.Close SaveChanges:=True
End Sub

Key Notes to Avoid Future Errors #

• Every If that starts a block (with code on new lines) must have a matching End If.
• Single-line If statements (like If x > 5 Then MsgBox "Too big") don't need End If, but if you split them into multiple lines, you need to use the block structure.
• Use Dir() with wildcards (*) to easily find files containing your target string—this is more reliable than looping through every file manually for simple matches.

内容的提问来源于stack exchange，提问作者user9734243