Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

In the code below, there is a condition for Range("I:I") at the begining of the event…

  1. User

    In the code below, there is a condition for Range("I:I") at the begining of the event and also at the bottom of the code. How can I combine the two conditons to make the code more efficient,

    Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range Set rng = Intersect(Target, Range("I:I")) If Not rng Is Nothing Then For Each cell In rng If IsDate(cell.Value) Then If cell.Offset(0, -7).Value = "Service" Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." Exit Sub Else If Not doubleClickFlag Then MsgBox "Please change the Issue description ( column B ) as required." End If doubleClickFlag = False Exit Sub End If End If Next cell End If Set rng = Intersect(Target, Range("G:G")) If Not rng Is Nothing Then For Each cell In rng If cell.Value = "" Then Exit Sub ElseIf IsNumeric(cell.Value) Then If IsDate(cell.Offset(0, 2).Value) = False Then If Not doubleClickFlag Then MsgBox "Date Service Completed not present" End If doubleClickFlag = False Exit Sub ElseIf cell.Offset(0, -5).Value <> "Service" Then Exit Sub Else MsgBox "A New Service row will now be created" cell.Offset(0, -5).Value = "Serviced" cell.Offset(0, 1).Value = "" Dim newRow As Long newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1 Range("B" & newRow).Value = "Service" Range("C" & newRow).Value = cell.Offset(0, 2).Value Range("F" & newRow).Value = cell.Offset(0, -1).Value Range("J" & newRow).Value = cell.Offset(0, 3).Value Range("L" & newRow).Value = cell.Offset(0, 5).Value Range("H" & newRow).Value = cell.Offset(0, 2).Value + cell.Value Exit Sub End If End If Next cell End If If Target.Column = 5 Then ColumnList Target End If If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("I:I")) Is Nothing Then If IsDate(Target.Value) Then If Range("B" & Target.Row).Value <> "Request" Then Exit Sub Else Range("B" & Target.Row).Select MsgBox "Please change 'Request' to the relevant Task Issue" Exit Sub End If End If End If End Sub

  2. Assistant

    You can combine the two conditions by using an OR operator in the IF statement. Here is the modified code:

    Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range Set rng = Intersect(Target, Range("I:I")) If Not rng Is Nothing Or Not Intersect(Target, Range("G:G")) Is Nothing Then For Each cell In rng If IsDate(cell.Value) Then If cell.Offset(0, -7).Value = "Service" Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." Exit Sub Else If Not doubleClickFlag Then MsgBox "Please change the Issue description ( column B ) as required." End If doubleClickFlag = False Exit Sub End If End If Next cell

        For Each cell In rng
            If cell.Value = "" Then
                Exit Sub
            ElseIf IsNumeric(cell.Value) Then
                If IsDate(cell.Offset(0, 2).Value) = False Then
                    If Not doubleClickFlag Then
                        MsgBox "Date Service Completed not present"
                    End If
                    doubleClickFlag = False
                    Exit Sub
                ElseIf cell.Offset(0, -5).Value <> "Service" Then
                    Exit Sub
                Else
                    MsgBox "A New Service row will now be created"
                    cell.Offset(0, -5).Value = "Serviced"
                    cell.Offset(0, 1).Value = ""
                    Dim newRow As Long
                    newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
                    Range("B" & newRow).Value = "Service"
                    Range("C" & newRow).Value = cell.Offset(0, 2).Value
                    Range("F" & newRow).Value = cell.Offset(0, -1).Value
                    Range("J" & newRow).Value = cell.Offset(0, 3).Value
                    Range("L" & newRow).Value = cell.Offset(0, 5).Value
                    Range("H" & newRow).Value = cell.Offset(0, 2).Value + cell.Value
                    Exit Sub
                End If
            End If
        Next cell
    End If
    
    If Target.Column = 5 Then
        ColumnList Target
    End If
    
    If Target.CountLarge > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("I:I")) Is Nothing Then
        If IsDate(Target.Value) Then
            If Range("B" & Target.Row).Value <> "Request" Then
                Exit Sub
            Else
                Range("B" & Target.Row).Select
                MsgBox "Please change 'Request' to the relevant Task Issue"
                Exit Sub
            End If
        End If
    End If
    

    End Sub

  3. User

    What I was trying to explain is how can I cobine these two codes that are triggered by the intersect of "I:I" into one.

    Dim rng As Range Dim cell As Range Set rng = Intersect(Target, Range("I:I")) If Not rng Is Nothing Then For Each cell In rng If IsDate(cell.Value) Then If cell.Offset(0, -7).Value = "Service" Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." Exit Sub Else If Not doubleClickFlag Then MsgBox "Please change the Issue description ( column B ) as required." End If doubleClickFlag = False Exit Sub End If End If Next cell End If

    If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("I:I")) Is Nothing Then If IsDate(Target.Value) Then If Range("B" & Target.Row).Value <> "Request" Then Exit Sub Else Range("B" & Target.Row).Select MsgBox "Please change 'Request' to the relevant Task Issue" Exit Sub

  4. Assistant

    To combine the two sections of code, you can remove the second condition and duplicate the first section of code within the second section. Here is the modified code:

    Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range Set rng = Intersect(Target, Range("I:I")) If Not rng Is Nothing Then For Each cell In rng If IsDate(cell.Value) Then If cell.Offset(0, -7).Value = "Service" Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." Exit Sub Else If Not doubleClickFlag Then MsgBox "Please change the Issue description ( column B ) as required." End If doubleClickFlag = False Exit Sub End If End If

            If cell.Value <> "Request" Then
                MsgBox "Please change 'Request' to the relevant Task Issue"
                Range("B" & cell.Row).Select
                Exit Sub
            End If
        Next cell
    End If
    
    If Target.Column = 5 Then
        ColumnList Target
    End If
    
    If Target.CountLarge > 1 Then Exit Sub
    

    End Sub

    By combining the two sections, the code will first check for changes in column I and perform the necessary actions. Then, it will check if the value in column I is "Request" and display a message if it is not. This way, you are able to handle both scenarios within one block of code.

  5. User

    In thethis code, I am getting an error 'For already in use' : Private Sub Worksheet_Change(ByVal Target As Range) Dim rngI As Range Dim rngG As Range Dim cell As Range

    Set rngI = Intersect(Target, Range("I6:I")) If Not rngI Is Nothing Then For Each cell In rngI If IsDate(cell.Value) Then If cell.Offset(0, -7).Value = "Service" Then If Not doubleClickFlag Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." Else MsgBox "Please change the Issue description ( column B ) as required." End If End If End If doubleClickFlag = False Exit For Exit Sub

    Set rngG = Intersect(Target, Range("G6:G")) For Each cell In rngG If cell.Value = "" Then Exit Sub ElseIf IsNumeric(cell.Value) Then If IsDate(cell.Offset(0, 2).Value) = False Then If Not doubleClickFlag Then MsgBox "Date Service Completed not present" End If doubleClickFlag = False Exit Sub ElseIf cell.Offset(0, -5).Value <> "Service" Then Exit Sub Else MsgBox "A New Service row will now be created" cell.Offset(0, -5).Value = "Serviced" cell.Offset(0, 1).Value = "" Dim newRow As Long newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1 Range("B" & newRow).Value = "Service" Range("C" & newRow).Value = cell.Offset(0, 2).Value Range("F" & newRow).Value = cell.Offset(0, -1).Value Range("J" & newRow).Value = cell.Offset(0, 3).Value Range("L" & newRow).Value = cell.Offset(0, 5).Value Range("H" & newRow).Value = cell.Offset(0, 2).Value + cell.Value Exit Sub End If End If Next cell End If

    If Target.Column = 5 Then ColumnList Target End If

    End Sub

  6. Assistant

    The error "For already in use" occurs because you have two For Each loops in the code without a corresponding Next statement. Additionally, you have placed an Exit For statement before the Exit Sub statement, which means the code will exit the loop prematurely and never reach the Exit Sub statement.

    To fix this, you need to properly structure the loop and exit statements. Here is the modified code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngI As Range
        Dim rngG As Range
        Dim cell As Range
    
        Set rngI = Intersect(Target, Range("I6:I"))
        If Not rngI Is Nothing Then
            For Each cell In rngI
                If IsDate(cell.Value) Then
                    If cell.Offset(0, -7).Value = "Service" Then
                        If Not doubleClickFlag Then
                            MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days."
                        Else
                            MsgBox "Please change the Issue description ( column B ) as required."
                        End If
                    End If
                    doubleClickFlag = False
                    Exit For
                End If
            Next cell
        End If
    
        Set rngG = Intersect(Target, Range("G6:G"))
        If Not rngG Is Nothing Then
            For Each cell In rngG
                If cell.Value = "" Then
                    Exit Sub
                ElseIf IsNumeric(cell.Value) Then
                    If IsDate(cell.Offset(0, 2).Value) = False Then
                        If Not doubleClickFlag Then
                            MsgBox "Date Service Completed not present"
                        End If
                        doubleClickFlag = False
                        Exit Sub
                    ElseIf cell.Offset(0, -5).Value <> "Service" Then
                        Exit Sub
                    Else
                        MsgBox "A New Service row will now be created"
                        cell.Offset(0, -5).Value = "Serviced"
                        cell.Offset(0, 1).Value = ""
                        Dim newRow As Long
                        newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
                        Range("B" & newRow).Value = "Service"
                        Range("C" & newRow).Value = cell.Offset(0, 2).Value
                        Range("F" & newRow).Value = cell.Offset(0, -1).Value
                        Range("J" & newRow).Value = cell.Offset(0, 3).Value
                        Range("L" & newRow).Value = cell.Offset(0, 5).Value
                        Range("H" & newRow).Value = cell.Offset(0, 2).Value + cell.Value
                        Exit Sub
                    End If
                End If
            Next cell
        End If
    
        If Target.Column = 5 Then
            ColumnList Target
        End If
    
    End Sub
    

    In the modified code, the Exit For statement is moved after the necessary actions inside the loop, and the Exit Sub statement is placed outside the loop after the second condition. This ensures that the loop is properly terminated and the code continues to execute the remaining statements outside the loop.