Block IF without End If error when hiding columns-CodePudding

in the below code i am getting the block if without end if error. It is basically just saying if the cell value =X then hide these cells, it then goes through different cell values and hide different cells when needed. Any help would be much appreciated.

   Dim CheckRange As Range
   Dim aCell As Range
     
   
   Set CheckRange = Intersect(Target, Range("C4"))
   
        Application.ScreenUpdating = False
   
        If Not CheckRange Is Nothing Then
        For Each aCell In CheckRange
        
            Select Case aCell.Address
            
            Case "$C$4"
            If aCell.Value = "" Then
                Sheets("Data input").Range("greenfield").EntireRow.Hidden = True
                Sheets("Data input").Range("brownfield").EntireRow.Hidden = True
                Exit For
            End If
            If aCell.Value = "Greenfield" Then
                Sheets("Data input").Range("greenfield").EntireRow.Hidden = False
                Sheets("Data input").Range("brownfield").EntireRow.Hidden = True
                Exit For
            End If
            If aCell.Value = "Brownfield" Then
                Sheets("Data input").Range("greenfield").EntireRow.Hidden = True
                Sheets("Data input").Range("brownfield").EntireRow.Hidden = False
                Exit For
            End If
   
   Set CheckRange = Intersect(Target, Range("C28,D29,D48"))
   
        Application.ScreenUpdating = False
   
        If Not CheckRange Is Nothing Then
      
        
            Select Case aCell.Address
            
            Case "$C$28"
            If aCell.Value = "" Then
                Sheets("Data input").Range("press1table").EntireRow.Hidden = True
                Sheets("Data input").Range("press1stages").EntireRow.Hidden = True
                Sheets("Data input").Range("press1input2").ClearContents
                Sheets("Data input").Range("press1input10").ClearContents
                Sheets("Data input").Range("press1input11").ClearContents
                Sheets("Data input").Range("press1input12").ClearContents
                Sheets("Data input").Range("press1input13").ClearContents
                Sheets("Data input").Range("press1input14").ClearContents
                Exit For
            End If
            If aCell.Value = "Manual ops" Then
                Sheets("Data input").Range("press1table").EntireRow.Hidden = True
                Sheets("Data input").Range("processcount1").EntireRow.Hidden = False
                Sheets("Data input").Range("press1stages").EntireRow.Hidden = True
                Sheets("Data input").Range("press1input2").ClearContents
                Sheets("Data input").Range("press1input10").ClearContents
                Sheets("Data input").Range("press1input11").ClearContents
                Sheets("Data input").Range("press1input12").ClearContents
                Sheets("Data input").Range("press1input13").ClearContents
                Sheets("Data input").Range("press1input14").ClearContents
                Exit For
            End If
            If aCell.Value = "Manual ops (ganged)" Then
                Sheets("Data input").Range("press1table").EntireRow.Hidden = True
                Sheets("Data input").Range("processcount1").EntireRow.Hidden = False
                Sheets("Data input").Range("press1stages").EntireRow.Hidden = True
                Sheets("Data input").Range("press1input2").ClearContents
                Sheets("Data input").Range("press1input10").ClearContents
                Sheets("Data input").Range("press1input11").ClearContents
                Sheets("Data input").Range("press1input12").ClearContents
                Sheets("Data input").Range("press1input13").ClearContents
                Sheets("Data input").Range("press1input14").ClearContents
                Exit For
            End If
            If aCell.Value = "Progression press" Then
                Sheets("Data input").Range("processcount1").EntireRow.Hidden = True
                Sheets("Data input").Range("press1stages").EntireRow.Hidden = False
                Sheets("Data input").Range("pressproctable1").EntireRow.Hidden = False
                Sheets("Data input").Range("prog1").EntireRow.Hidden = False
                Sheets("Data input").Range("trans1").EntireRow.Hidden = True
                Sheets("Data input").Range("tand1").EntireRow.Hidden = True
                Sheets("Data input").Range("manproc15").EntireRow.Hidden = True
                Sheets("Data input").Range("secondops1").EntireRow.Hidden = True
                Sheets("Data input").Range("press1input2").ClearContents
                Sheets("Data input").Range("press1input10").ClearContents
                Sheets("Data input").Range("press1input11").ClearContents
                Sheets("Data input").Range("press1input12").ClearContents
                Sheets("Data input").Range("press1input13").ClearContents
                Sheets("Data input").Range("press1input14").ClearContents
                Exit For
            End If
            End Select
            End Sub

CodePudding user response：

Seems you are missing End If for two lines..

If Not CheckRange Is Nothing Then
'For Each aCell In CheckRange
.....
....
....
End if

and also this part is missing end if:

If Not CheckRange Is Nothing Then
'Select Case aCell.Address
.....
....
....
End if

Also take an extra look at Select Case, it look unfinished.

I recommend to fix your code layout. For example this is a helpful tool: https://www.automateexcel.com/vba-code-indenter/

CodePudding user response：

A Worksheet Change for Hiding Rows

The following code has to be copied to a sheet module and is triggered automatically on each change (manually (writing, pasting) or via VBA) in one or more of the critical cells ("C4,C28,D29,D48").
It is assumed that the worksheet Data input where the hiding and the clearing of contents will take place, is another worksheet.
After each change, take a look at the results in VBE's Immediate window (Ctrl G). When done testing, out-comment or delete the Debug.Print lines.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Debug.Print "A worksheet change happened..."
    
    Dim CheckRange As Range
    Set CheckRange = Intersect(Target, Range("C4,C28,D29,D48"))
    If CheckRange Is Nothing Then Exit Sub
        
    Debug.Print "The address of the intersecting range is """ _
        & CheckRange.Address(0, 0) & """."
        
    ' One cell is expected but just in case there are more, use only the first.
    Dim CheckCell As Range: Set CheckCell = CheckRange.Cells(1)
    Dim CheckString As String: CheckString = CStr(CheckCell.Value)
    
    Debug.Print "The check string is equal to """ & CheckString & """."
    
    On Error GoTo ClearError
    
    Application.ScreenUpdating = False
    ' It is assumed that the worksheet "Data input" is another worksheet.
    ' If you plan on writing to the worksheet whose module contains this code,
    ' the following line is crucial to avoid an endless loop (Excel crashing).
    'Application.EnableEvents = False ' ***
    
    Dim ws As Worksheet: Set ws = Worksheets("Data input")
    
    Select Case CheckCell.Address(0, 0)
    
    Case "C4"
        
        Debug.Print "Entering 'C4' Select statement..."
        
        Select Case CheckString
        Case ""
            ws.Range("greenfield").EntireRow.Hidden = True
            ws.Range("brownfield").EntireRow.Hidden = True
        Case "Greenfield"
            ws.Range("greenfield").EntireRow.Hidden = False
            ws.Range("brownfield").EntireRow.Hidden = True
        Case "Brownfield"
            ws.Range("greenfield").EntireRow.Hidden = True
            ws.Range("brownfield").EntireRow.Hidden = False
        End Select
    
    Case "C28", "D29", "D48"
        
        Debug.Print "Entering 'C28,D29,D48' Select statement..."
        
        Select Case CheckString
        Case ""
            ws.Range("press1table").EntireRow.Hidden = True
            ws.Range("press1stages").EntireRow.Hidden = True
        Case "Manual ops"
            ws.Range("press1table").EntireRow.Hidden = True
            ws.Range("processcount1").EntireRow.Hidden = False
            ws.Range("press1stages").EntireRow.Hidden = True
        Case "Manual ops (ganged)"
            ws.Range("press1table").EntireRow.Hidden = True
            ws.Range("processcount1").EntireRow.Hidden = False
            ws.Range("press1stages").EntireRow.Hidden = True
        Case "Progression press"
            ws.Range("processcount1").EntireRow.Hidden = True
            ws.Range("press1stages").EntireRow.Hidden = False
            ws.Range("pressproctable1").EntireRow.Hidden = False
            ws.Range("prog1").EntireRow.Hidden = False
            ws.Range("trans1").EntireRow.Hidden = True
            ws.Range("tand1").EntireRow.Hidden = True
            ws.Range("manproc15").EntireRow.Hidden = True
            ws.Range("secondops1").EntireRow.Hidden = True
        End Select
        
        Debug.Print "Entering 'ClearContents' Select statement..."
        
        Select Case CheckString
        Case "", "Manual ops", "Manual ops (ganged)", "Progression press"
            ws.Range("press1input2").ClearContents
            ws.Range("press1input10").ClearContents
            ws.Range("press1input11").ClearContents
            ws.Range("press1input12").ClearContents
            ws.Range("press1input13").ClearContents
            ws.Range("press1input14").ClearContents
        End Select
    
    End Select

SafeExit:
    'Application.EnableEvents = True ' *** Read about it above.
    Application.ScreenUpdating = True

    Exit Sub
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume SafeExit
End Sub

CodePudding user response：

It can be very hard to keep track of your block closures if you have a lot of code in each block. The best way to deal with this is to restructure your code so that blocks of code become function or sub calls. In that way you get a much better overview of your code structure.

I have no idea if the code below runs as you intended, it is just an example to show hw to move code to subs/functions to make you code structure easier to comprehend

Option Explicit


Public Sub CheckRange()

    Dim CheckRange As Range
    Dim aCell As Range
     
   
    Set CheckRange = Intersect(Target, Range("C4"))
   
    Application.ScreenUpdating = False
   
    If Not CheckRange Is Nothing Then
    
        For Each aCell In CheckRange
        
            Select Case aCell.Address
            
                If CheckC24 Then Exit For
   
                 Set CheckRange = Intersect(Target, Range("C28,D29,D48"))

                 Application.ScreenUpdating = False

                 If Not CheckRange Is Nothing Then
   
                     CheckC28
                     
                 End If
                 
            End Select
                        
        Next

    end if
        
End Sub

Public Function CheckC24(ByVal ipcell As Range) As Boolean

    check24 = False
    If ipcell.Address <> "$C$24" Then Exit Function
    
    If ipcell.Value = "" Then
    
        Sheets("Data input").Range("greenfield").EntireRow.Hidden = True
        Sheets("Data input").Range("brownfield").EntireRow.Hidden = True
        check24 = True
        
    End If
    
    If ipcell.Value = "Greenfield" Then
        Sheets("Data input").Range("greenfield").EntireRow.Hidden = False
        Sheets("Data input").Range("brownfield").EntireRow.Hidden = True
        check24 = True
        
    End If
    
    If ipcell.Value = "Brownfield" Then
    
        Sheets("Data input").Range("greenfield").EntireRow.Hidden = True
        Sheets("Data input").Range("brownfield").EntireRow.Hidden = False
        check24 = True
        
    End If
    
End Function

Public Sub CheckC28(ByVal ipcell As Range)

    If ipcell.Address <> "$C$28" Then Exit Sub
    
    If ipcell.Value = "" Then C28IsNothing
                                
    If ipcell.Value = "Manual ops" Then C28IsManualOps
    
    If ipcell.Value = "Manual ops (ganged)" Then C28IsManualOpsGanged
     
    If ipcell.Value = "Progression press" Then C28IsProgressionPress
                                

End Sub

'For testing C28 you might want to separate out the common statements

Public Sub C28Common()

    Sheets("Data input").Range("press1input2").ClearContents
    Sheets("Data input").Range("press1input10").ClearContents
    Sheets("Data input").Range("press1input11").ClearContents
    Sheets("Data input").Range("press1input12").ClearContents
    Sheets("Data input").Range("press1input13").ClearContents
    Sheets("Data input").Range("press1input14").ClearContents
    
End Sub

Public SubC28IsNothing()

    C28Common
    Sheets("Data input").Range("press1table").EntireRow.Hidden = True
    Sheets("Data input").Range("press1stages").EntireRow.Hidden = True
    

End Sub
                                  

Public Function C28IsManualOps()

    C28Common
    Sheets("Data input").Range("press1table").EntireRow.Hidden = True
    Sheets("Data input").Range("processcount1").EntireRow.Hidden = False
    Sheets("Data input").Range("press1stages").EntireRow.Hidden = True
    
End Sub

Public Sub C28IsManualOpGanged()

    C28Common
    Sheets("Data input").Range("press1table").EntireRow.Hidden = True
    Sheets("Data input").Range("processcount1").EntireRow.Hidden = False
    Sheets("Data input").Range("press1stages").EntireRow.Hidden = True
    
End Sub
                                
 Public Sub C28IsProgressionPress()
 
    C28Common
    Sheets("Data input").Range("processcount1").EntireRow.Hidden = True
    Sheets("Data input").Range("press1stages").EntireRow.Hidden = False
    Sheets("Data input").Range("pressproctable1").EntireRow.Hidden = False
    Sheets("Data input").Range("prog1").EntireRow.Hidden = False
    Sheets("Data input").Range("trans1").EntireRow.Hidden = True
    Sheets("Data input").Range("tand1").EntireRow.Hidden = True
    Sheets("Data input").Range("manproc15").EntireRow.Hidden = True
    Sheets("Data input").Range("secondops1").EntireRow.Hidden = True
   
End Sub