In my excel spread sheet, I have 8 ranges. D3:D18, E3:I18, L3:L18, M3:Q18, T3:T18,…
- User
In my excel spread sheet, I have 8 ranges.
D3:D18, E3:I18, L3:L18, M3:Q18, T3:T18, U3:Y18, AB3:AB18, AC3:AG18.
I would like VBA codes to handle the following conditions.
When a cell value in the range AC3:AG18 is changed to 'y', determine the value of AB3:AB18 of the same row. determine also the offset position of each 'y' from AB3:AB18. Find the same value in range AB3:AB18 in the ranges D3:D18, L3:L18, T3:T18, and where the match is found, change the row cell value to 'y' to correspond with the same offset position with AB3:AB18.
- Assistant
You can use the following VBA code to handle the conditions you mentioned:
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 = rngOffset.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) resultOffset.Value = "y" End If End If Next cell Application.EnableEvents = True End SubYou need to place this code in the VBA module of the worksheet where your data is located. To do this, follow these steps:
- Right-click on the sheet tab.
- Select "View Code" from the context menu.
- In the VBA editor, paste the above code into the code window.
This code uses the
Worksheet_Changeevent to trigger when a value in the range AC3:AG18 is changed. It then checks if the changed value is "y". If it is, it determines the corresponding value in AB3:AB18 and calculates the offset position. It then searches for the value in the ranges D3:D18, L3:L18, and T3:T18 and changes the cell to "y" at the corresponding offset position.Note: This code assumes that you have a single sheet with the specified ranges. If you need to apply this code to different sheets or ranges, you'll need to modify the code accordingly.
- User
I believe that the look up value of AB3:AB18 should not be - lookupValue = rngOffset.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18. This line is looking at AA3:AA18
- Assistant
Apologies for the confusion. You're correct, the lookup value should be taken from column AB in the range AB3:AB18. Here's the corrected line of code:
lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18Please replace the previous line I provided with this one in the code.
- User
The VBA works brilliantly
- Assistant
I'm glad to hear that the VBA code works well for you! If you have any further questions or need assistance with anything else, feel free to ask.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.