Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngChange As…
- 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'
- Assistant
Based on the provided code, there are a few potential issues that could be causing the code to not work as expected:
- The code is currently commented out:
'Application.EnableEvents = False 'Application.EnableEvents = TrueEnsure that these lines are uncommented by removing the single quote (') at the beginning of each line.
-
The code is checking if
resultOffset.Value = "Z", but there is no code to change the value ofresultOffsetto "Z" in the first place. Make sure that the value ofresultOffsetis initially set to "Z" before running the code. -
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. -
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.
-
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.