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

Private Sub Worksheet_Change(ByVal Target As Range) Dim rngChange As Range Set rngChange…

  1. User

    Private Sub Worksheet_Change(ByVal Target As Range) Dim rngChange As Range

    Set rngChange = Intersect(Target, Me.Range("AC3:AG18")) If Not rngChange Is Nothing Then Call ThisWeek End If

    End Sub

    Sub ThisWeek()

    Dim rngChange As Range Dim rngValues As Range Dim rngOffset As Range

    Set rngChange = Intersect(Target, Me.Range("AC3:AG18")) Set rngValues = Me.Range("AB3:AB18") Set rngOffset = rngValues.Offset(, -1) ' Offset by 1 column to the left (AB -> AA)

    Dim cell As Range For Each cell In rngChange If cell.Value = "b" Then Dim rowNum As Long rowNum = cell.Row - rngChange.Rows(1).Row + 1 ' Calculate the relative row number

    Dim lookupValue As Variant lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18

    Dim resultRng As Range Set resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))

    Dim resultCell As Range Set resultCell = resultRng.Find(lookupValue, LookIn:=xlValues) If Not resultCell Is Nothing Then Dim offsetVal As Long offsetVal = rngChange.Columns(1).Column - rngOffset.Columns(1).Column - 1 ' Calculate the offset

    Dim resultOffset As Range Set resultOffset = resultCell.Offset(, offsetVal)

    If resultOffset.Value = "z" Then resultOffset.Value = "y" Else MsgBox "Current Bay is not Free on this day. Only Paking Spaces marked 'z' are free on the day" cell.Value = "" ' Reset the rejected "b" value to empty Exit Sub End If End If End If Next cell

    End Sub . . . I am getting an error on this line of code in the Sub ThisWeek(); Set rngChange = Intersect(Target, Me.Range("AC3:AG18"))
    
  2. Assistant

    The error is occurring because the variable "Target" is not declared in the Sub ThisWeek(). You need to pass the "Target" variable from the Worksheet_Change event to the ThisWeek() subroutine.

    To fix the error, add the "Target" parameter to the ThisWeek() subroutine like this:

    Sub ThisWeek(Target As Range)

    Then, when calling the ThisWeek() subroutine from the Worksheet_Change event, pass the "Target" variable as an argument like this:

    Call ThisWeek(Target)

    This way, the "Target" variable will be available in the ThisWeek() subroutine and the error should be resolved.

  3. User

    Please can you write the entire code, I do not understand your explanation

  4. Assistant

    Certainly! Here's the updated code with the necessary changes:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngChange As Range
    
        Set rngChange = Intersect(Target, Me.Range("AC3:AG18"))
        If Not rngChange Is Nothing Then
            Call ThisWeek(rngChange)
        End If
    End Sub
    
    Sub ThisWeek(rngChange As Range)
        Dim rngValues As Range
        Dim rngOffset As Range
    
        Set rngValues = Me.Range("AB3:AB18")
        Set rngOffset = rngValues.Offset(, -1) ' Offset by 1 column to the left (AB -> AA)
    
        Dim cell As Range
        For Each cell In rngChange
            If cell.Value = "b" Then
                Dim rowNum As Long
                rowNum = cell.Row - rngChange.Rows(1).Row + 1 ' Calculate the relative row number
    
                Dim lookupValue As Variant
                lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18
    
                Dim resultRng As Range
                Set resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))
    
                Dim resultCell As Range
                Set resultCell = resultRng.Find(lookupValue, LookIn:=xlValues)
                If Not resultCell Is Nothing Then
                    Dim offsetVal As Long
                    offsetVal = rngChange.Columns(1).Column - rngOffset.Columns(1).Column - 1 ' Calculate the offset
    
                    Dim resultOffset As Range
                    Set resultOffset = resultCell.Offset(, offsetVal)
    
                    If resultOffset.Value = "z" Then
                        resultOffset.Value = "y"
                    Else
                        MsgBox "Current Bay is not Free on this day. Only Parking Spaces marked 'z' are free on the day."
                        cell.Value = "" ' Reset the rejected "b" value to empty
                        Exit Sub
                    End If
                End If
            End If
        Next cell
    End Sub
    

    In the updated code, the Worksheet_Change event is passing the rngChange range as an argument to the ThisWeek() subroutine. The ThisWeek() subroutine now has a rngChange parameter to receive the range passed from the event. This ensures that the rngChange variable is declared and available for use in the ThisWeek() subroutine.

  5. User

    How can I make this work: Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngChange As Range
    
    Set rngChange = Intersect(Target, Me.Range("AC3:AG18"))
    If Not rngChange Is Nothing Then
        Call ThisWeek(rngChange)
    End If
    
        Set rngChange = Intersect(Target, Me.Range("AC22:AG37"))
    If Not rngChange Is Nothing Then
        Call NextWeek(rngChange)
    End If
    

    End Sub

    Sub ThisWeek(rngChange As Range) Dim rngValues As Range Dim rngOffset As Range

    Set rngValues = Me.Range("AB3:AB18")
    Set rngOffset = rngValues.Offset(, -1) ' Offset by 1 column to the left (AB -> AA)
    
    Dim cell As Range
    For Each cell In rngChange
        If cell.Value = "b" Then
            Dim rowNum As Long
            rowNum = cell.Row - rngChange.Rows(1).Row + 1 ' Calculate the relative row number
    
            Dim lookupValue As Variant
            lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18
    
            Dim resultRng As Range
            Set resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))
    
            Dim resultCell As Range
            Set resultCell = resultRng.Find(lookupValue, LookIn:=xlValues)
            If Not resultCell Is Nothing Then
                Dim offsetVal As Long
                offsetVal = rngChange.Columns(1).Column - rngOffset.Columns(1).Column - 1 ' Calculate the offset
    
                Dim resultOffset As Range
                Set resultOffset = resultCell.Offset(, offsetVal)
    
                If resultOffset.Value = "z" Then
                    resultOffset.Value = "y"
                Else
                    MsgBox "Current Bay is not Free on this day. Only Parking Spaces marked 'z' are free on the day."
                    cell.Value = "" ' Reset the rejected "b" value to empty
                    Exit Sub
                End If
            End If
        End If
    Next cell
    

    End Sub

    Sub NextWeek(rngChange As Range) Dim rngValues As Range Dim rngOffset As Range

    Set rngValues = Me.Range("AB22:AB37")
    Set rngOffset = rngValues.Offset(, -1) ' Offset by 1 column to the left (AB -> AA)
    
    Dim cell As Range
    For Each cell In rngChange
        If cell.Value = "b" Then
            Dim rowNum As Long
            rowNum = cell.Row - rngChange.Rows(1).Row + 1 ' Calculate the relative row number
    
            Dim lookupValue As Variant
            lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18
    
            Dim resultRng As Range
            Set resultRng = Union(Me.Range("D22:D37"), Me.Range("L22:L37"), Me.Range("T22:T37"))
    
            Dim resultCell As Range
            Set resultCell = resultRng.Find(lookupValue, LookIn:=xlValues)
            If Not resultCell Is Nothing Then
                Dim offsetVal As Long
                offsetVal = rngChange.Columns(1).Column - rngOffset.Columns(1).Column - 1 ' Calculate the offset
    
                Dim resultOffset As Range
                Set resultOffset = resultCell.Offset(, offsetVal)
    
                If resultOffset.Value = "z" Then
                    resultOffset.Value = "y"
                Else
                    MsgBox "Current Bay is not Free on this day. Only Parking Spaces marked 'z' are free on the day."
                    cell.Value = "" ' Reset the rejected "b" value to empty
                    Exit Sub
                End If
            End If
        End If
    Next cell
    

    End Sub

  6. Assistant

    The code you provided should work as expected. It first checks if the changed range intersects with the range "AC3:AG18" and if so, calls the ThisWeek() subroutine with the intersected range. Then, it checks if the changed range intersects with the range "AC22:AG37" and if so, calls the NextWeek() subroutine with the intersected range.

    The ThisWeek() subroutine will handle the first range ("AC3:AG18"), and the NextWeek() subroutine will handle the second range ("AC22:AG37"). Each subroutine performs the necessary calculations and validations for the corresponding range.

    Make sure you have placed the code in the appropriate worksheet module, such as "Sheet1" or "Sheet2", depending on where you want the code to be executed. Also, ensure that you have properly labeled the parking bay values ("b", "z", "y") in the respective ranges mentioned in the code.

    If you encounter any specific issues or errors, please let me know, and I'll be happy to assist you further.