In the code below, there is a condition for Range("I:I") at the begining of the event…
- 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
- 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 IfEnd Sub
- 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
- 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 SubEnd 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.
- 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
- Assistant
The error "For already in use" occurs because you have two
For Eachloops in the code without a correspondingNextstatement. Additionally, you have placed anExit Forstatement before theExit Substatement, which means the code will exit the loop prematurely and never reach theExit Substatement.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 SubIn the modified code, the
Exit Forstatement is moved after the necessary actions inside the loop, and theExit Substatement 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.