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

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

  1. User

    Option Explicit

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

    Set rngChange = Intersect(Target, Me.Range("AC3:AG18")) If rngChange Is Nothing Then Exit Sub

    'Application.EnableEvents = False

    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 = "y" 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 ' 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" Exit Sub End If End If End If Next cell

    'Application.EnableEvents = True End Sub ........ The code is not working. When I enter 'y' in range AB3:AG18 the corresponding value in the other range where the match is found does not change the value to 'y'

  2. Assistant

    Based on the provided code, there are a few potential issues that could be causing the code to not work as expected:

    1. The code is currently commented out:
    'Application.EnableEvents = False
    'Application.EnableEvents = True
    

    Ensure that these lines are uncommented by removing the single quote (') at the beginning of each line.

    1. The code is checking if resultOffset.Value = "Z", but there is no code to change the value of resultOffset to "Z" in the first place. Make sure that the value of resultOffset is initially set to "Z" before running the code.

    2. The Private Sub Worksheet_Change(ByVal Target As Range) event code is tied to the worksheet where it is written. Make sure that the code is written in the appropriate worksheet module.

    3. Check that the worksheet that contains the data is the one where the code is written. If the code is written in one worksheet's module but the data is on another worksheet, the code will not work.

    4. Verify that the ranges specified ("AC3:AG18", "AB3:AB18", "D3:D18", "L3:L18", "T3:T18") match the actual ranges where the values should be changed.

    By addressing these potential issues, the code should be able to correctly change the corresponding values to "y" when "y" is entered in range AB3:AG18.