Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

Is this correct: Dim rng1 As Range Set rng1 = Range("F6:F206") ' Check if the changed…

  1. User

    Is this correct: Dim rng1 As Range Set rng1 = Range("F6:F206") ' Check if the changed cells are within the specified range If Not Intersect(Target, rng1) Is Nothing Then If Target.Value = "" Then Exit Sub Else MsgBox "When entering the Return Date" & vbNewLine & "" & vbNewLine & _ "Always Enter Actual Date of Return", vbInformation, "RETURN DATE" End If

  2. Assistant

    Your code snippet appears to be a part of a VBA (Visual Basic for Applications) routine, likely designed to respond to changes in a specific range of cells. You have the right idea, but there are a couple of issues and improvements that can be made.

    1. Variable Declaration for Target: The Target variable is usually declared when you're working within event procedures (like Worksheet_Change). Ensure that you have the correct context or variable declaration for Target.

    2. Logical Structure: The logic within the If block needs a small adjustment, particularly for the Else clause. It seems like the message box should only display if the cell is not empty.

    Here is a corrected version of your code snippet, assuming it is within a Worksheet_Change event:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng1 As Range
        Set rng1 = Range("F6:F206")
        
        ' Check if the changed cells are within the specified range
        If Not Intersect(Target, rng1) Is Nothing Then
            If Target.Value = "" Then
                Exit Sub
            Else
                MsgBox "When entering the Return Date" & vbNewLine & vbNewLine & _
                "Always Enter Actual Date of Return", vbInformation, "RETURN DATE"
            End If
        End If
    End Sub
    

    Key Points:

    1. Event Handler: Make sure this code is placed in the appropriate worksheet code module (e.g., Sheet1, Sheet2) and that it is an event procedure. This will allow it to respond when changes are made to the worksheet.

    2. Exit Sub: The Exit Sub, when Target.Value is empty, will effectively stop further execution if a cell within rng1 is cleared.

    3. Message Box: The message box will now display only if the changed cell in rng1 contains a value after an edit, which fits the desired logic.

    Make sure the rest of your VBA project (including Option Explicit and other relevant procedures) is set up correctly for best practices and to avoid runtime errors.

  3. User

    I have an error 'Method Range of object '_Worksheet' failed. on this line: 'If Not Intersect(Target, Range(rangee(e))) Is Nothing Then' In the code below.

    Option Explicit

    Private Sub Worksheet_Activate() ThisWorkbook.Worksheets("HolCard").Unprotect Password:="edit" Application.Calculate ThisWorkbook.Worksheets("HolCard").Protect Password:="edit" End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range, rng6 As Range, rng7 As Range Set rng1 = Range("B3:B23") Set rng2 = Range("E3:E23") Set rng3 = Range("H3:H23") Set rng4 = Range("K3:K23") Set rng5 = Range("N3:N23") Set rng6 = Range("Q3:Q23") Set rng7 = Range("T3:T23") ' Check if the changed cells are within the specified range If Not Intersect(Target, rng1) Is Nothing Then Range("C3:C23").Calculate End If If Not Intersect(Target, rng2) Is Nothing Then Range("F3:F23").Calculate End If If Not Intersect(Target, rng3) Is Nothing Then Range("I3:I23").Calculate End If If Not Intersect(Target, rng4) Is Nothing Then Range("L3:L23").Calculate End If If Not Intersect(Target, rng5) Is Nothing Then Range("O3:O23").Calculate End If If Not Intersect(Target, rng6) Is Nothing Then Range("R3:R23").Calculate End If If Not Intersect(Target, rng7) Is Nothing Then Range("U3:U23").Calculate End If

    End Sub

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Count = 1 And Target.Column = 1 And Target.Row >= 3 And Target.Row <= 23 Then If IsEmpty(Target) Then Exit Sub If Not Target.Comment Is Nothing Then MsgBox "Date Already Recorded to Leave" Exit Sub Else Call Module5.ProcessLeaveRequestABC(Target) Cancel = True End If End If

    If Target.Count = 1 And Target.Column = 4 And Target.Row >= 3 And Target.Row <= 23 Then
        If IsEmpty(Target) Then Exit Sub
        If Not Target.Comment Is Nothing Then
        MsgBox "Date Already Recorded to Leave"
        Exit Sub
    Else
        Call Module5.ProcessLeaveRequestDEF(Target)
        Cancel = True
    End If
    

    End If

    If Target.Count = 1 And Target.Column = 7 And Target.Row >= 3 And Target.Row <= 23 Then
        If IsEmpty(Target) Then Exit Sub
        If Not Target.Comment Is Nothing Then
        MsgBox "Date Already Recorded to Leave"
        Exit Sub
    Else
        Call Module5.ProcessLeaveRequestGHI(Target)
        Cancel = True
    End If
    

    End If

    If Target.Count = 1 And Target.Column = 10 And Target.Row >= 3 And Target.Row <= 23 Then
        If IsEmpty(Target) Then Exit Sub
        If Not Target.Comment Is Nothing Then
        MsgBox "Date Already Recorded to Leave"
        Exit Sub
    Else
        Call Module5.ProcessLeaveRequestJKL(Target)
        Cancel = True
    End If
    

    End If

    If Target.Count = 1 And Target.Column = 13 And Target.Row >= 3 And Target.Row <= 23 Then
        If IsEmpty(Target) Then Exit Sub
        If Not Target.Comment Is Nothing Then
        MsgBox "Date Already Recorded to Leave"
        Exit Sub
    Else
        Call Module5.ProcessLeaveRequestMNO(Target)
        Cancel = True
    End If
    

    End If

    If Target.Count = 1 And Target.Column = 16 And Target.Row >= 3 And Target.Row <= 23 Then
        If IsEmpty(Target) Then Exit Sub
        If Not Target.Comment Is Nothing Then
        MsgBox "Date Already Recorded to Leave"
        Exit Sub
    Else
        Call Module5.ProcessLeaveRequestPQR(Target)
        Cancel = True
    End If
    

    End If

    If Target.Count = 1 And Target.Column = 19 And Target.Row >= 3 And Target.Row <= 23 Then
        If IsEmpty(Target) Then Exit Sub
        If Not Target.Comment Is Nothing Then
        MsgBox "Date Already Recorded to Leave"
        Exit Sub
    Else
        Call Module5.ProcessLeaveRequestSTU(Target)
        Cancel = True
    End If
    

    End If

    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Target.Count > 1 Then
        Application.EnableEvents = True
        Exit Sub
    End If
    
    ' DATE END CHECK FOR WEEKEND OR PUBLIC HOLIDAY
    Dim rng As Range
    Set rng = Union(Range("B3:B23"), Range("E3:E23"), Range("H3:H23"), Range("K3:K23"), Range("N3:N23"), Range("Q3:Q23"), Range("T3:T23"))
    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Offset(0, -1).Value = "" Then
        Application.Undo
            MsgBox "Date Start must be entered first", vbCritical, "DATE START"
        End If
    End If
    
    Dim rangee() As Variant
    rangee = Array(Range("B3:B23"), Range("E3:E23"), Range("H3:H23"), Range("K3:K23"), Range("N3:N23"), Range("Q3:Q23"), Range("T3:T23"))
    Dim e As Long
    Dim foundeMatch As Boolean
    foundeMatch = False
    

    'Check if Target intersects with any range in the array For e = LBound(rangee) To UBound(rangee) If Not Intersect(Target, Range(rangee(e))) Is Nothing Then foundeMatch = True Exit For 'Exit the loop once a match is found End If Next e

    'If no match found, exit the subroutine If Not foundeMatch Then Application.EnableEvents = True Exit Sub End If

    'If a match is found, process the rest of the code For e = LBound(rangee) To UBound(rangee) If Not Intersect(Target, Range(rangee(e))) Is Nothing Then Dim dateEnd As Date dateEnd = Target.Value If Target.Value <> "" Then Call Module4.HolPubDateCheckEnd(dateEnd) Exit For 'Exit the loop once a match is found End If End If Next e

    ' DATE START CHECK FOR WEEKEND OR PUBLIC HOLIDAY
    Dim ranges() As Variant
    ranges = Array("A3:A23", "D3:D23", "G3:G23", "J3:J23", "M3:M23", "P3:P23", "S3:S23")
    Dim s As Long
    Dim foundsMatch As Boolean
    foundsMatch = False
    

    'Check if Target intersects with any range in the array For s = LBound(ranges) To UBound(ranges) If Not Intersect(Target, Range(ranges(s))) Is Nothing Then foundsMatch = True Exit For 'Exit the loop once a match is found End If Next s

    'If no match found, exit the subroutine If Not foundsMatch Then Application.EnableEvents = True Exit Sub End If

    'If a match is found, process the rest of the code For s = LBound(ranges) To UBound(ranges) If Not Intersect(Target, Range(ranges(s))) Is Nothing Then Dim dateStart As Date dateStart = Target.Value If Target.Value <> "" Then Call Module4.HolPubDateCheckStart(dateStart) Exit For 'Exit the loop once a match is found End If End If Next s Application.EnableEvents = True

    End Sub

  4. Assistant

    The error you're encountering, "Method Range of object '_Worksheet' failed," occurs when you're trying to access a Range object incorrectly. In the relevant part of your code, you're attempting to use the Range method on an array of Range objects, which is not correct.

    Problematic Code

    The problematic section is this part of your Worksheet_Change subroutine:

    Dim rangee() As Variant
    rangee = Array(Range("B3:B23"), Range("E3:E23"), Range("H3:H23"), Range("K3:K23"), Range("N3:N23"), Range("Q3:Q23"), Range("T3:T23"))
    

    Correcting the Code

    Instead of creating an array of Range objects directly using the Range method, you can simply use the addresses as strings in the array. Here's how you can define the rangee array correctly:

    Dim rangee() As String
    rangee = Array("B3:B23", "E3:E23", "H3:H23", "K3:K23", "N3:N23", "Q3:Q23", "T3:T23")
    

    Then, when you want to check the intersection, you can use it like this:

    For e = LBound(rangee) To UBound(rangee)
        If Not Intersect(Target, Me.Range(rangee(e))) Is Nothing Then
            foundeMatch = True
            Exit For 'Exit the loop once a match is found
        End If
    Next e
    

    Complete Corrected Block

    Here's the corrected block for the Worksheet_Change subroutine including just the relevant parts associated with rangee:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Application.EnableEvents = False
    
        If Target.Count > 1 Then
            Application.EnableEvents = True
            Exit Sub
        End If
    
        ' DATE END CHECK FOR WEEKEND OR PUBLIC HOLIDAY
        Dim rng As Range
        Set rng = Union(Range("B3:B23"), Range("E3:E23"), Range("H3:H23"), Range("K3:K23"), Range("N3:N23"), Range("Q3:Q23"), Range("T3:T23"))
        If Not Intersect(Target, rng) Is Nothing Then
            If Target.Offset(0, -1).Value = "" Then
                Application.Undo
                MsgBox "Date Start must be entered first", vbCritical, "DATE START"
            End If
        End If
    
        Dim rangee() As String
        rangee = Array("B3:B23", "E3:E23", "H3:H23", "K3:K23", "N3:N23", "Q3:Q23", "T3:T23")
        Dim e As Long
        Dim foundeMatch As Boolean
        foundeMatch = False
    
        ' Check if Target intersects with any range in the array
        For e = LBound(rangee) To UBound(rangee)
            If Not Intersect(Target, Me.Range(rangee(e))) Is Nothing Then
                foundeMatch = True
                Exit For ' Exit the loop once a match is found
            End If
        Next e
    
        ' If no match found, exit the subroutine
        If Not foundeMatch Then
            Application.EnableEvents = True
            Exit Sub
        End If
    
        ' If a match is found, process the rest of the code
        For e = LBound(rangee) To UBound(rangee)
            If Not Intersect(Target, Me.Range(rangee(e))) Is Nothing Then
                Dim dateEnd As Date
                dateEnd = Target.Value
                If Target.Value <> "" Then
                    Call Module4.HolPubDateCheckEnd(dateEnd)
                    Exit For 'Exit the loop once a match is found
                End If
            End If
        Next e
    
        ' DATE START CHECK FOR WEEKEND OR PUBLIC HOLIDAY
        Dim ranges() As String
        ranges = Array("A3:A23", "D3:D23", "G3:G23", "J3:J23", "M3:M23", "P3:P23", "S3:S23")
        Dim s As Long
        Dim foundsMatch As Boolean
        foundsMatch = False
    
        ' Check if Target intersects with any range in the array
        For s = LBound(ranges) To UBound(ranges)
            If Not Intersect(Target, Me.Range(ranges(s))) Is Nothing Then
                foundsMatch = True
                Exit For ' Exit the loop once a match is found
            End If
        Next s
    
        ' If no match found, exit the subroutine
        If Not foundsMatch Then
            Application.EnableEvents = True
            Exit Sub
        End If
    
        ' If a match is found, process the rest of the code
        For s = LBound(ranges) To UBound(ranges)
            If Not Intersect(Target, Me.Range(ranges(s))) Is Nothing Then
                Dim dateStart As Date
                dateStart = Target.Value
                If Target.Value <> "" Then
                    Call Module4.HolPubDateCheckStart(dateStart)
                    Exit For ' Exit the loop once a match is found
                End If
            End If
        Next s
    
        Application.EnableEvents = True
    
    End Sub
    

    Summary

    By using string addresses in your rangee array, you correctly specify the ranges for the Intersect method. Additionally, including Me. before Range ensures that you're accessing ranges within the current worksheet. This should resolve your issue and avoid run-time errors.