Private Sub Worksheet_Change(ByVal Target As Range) Dim rngChange As Range Set rngChange…
- 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")) - 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.
- User
Please can you write the entire code, I do not understand your explanation
- 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 SubIn the updated code, the Worksheet_Change event is passing the
rngChangerange as an argument to the ThisWeek() subroutine. The ThisWeek() subroutine now has arngChangeparameter to receive the range passed from the event. This ensures that therngChangevariable is declared and available for use in the ThisWeek() subroutine. - 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 IfEnd 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 cellEnd 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 cellEnd Sub
- 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.