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

In my excel spread sheet, I have 8 ranges. D3:D18, E3:I18, L3:L18, M3:Q18, T3:T18,…

  1. 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.

  2. 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 Sub
    

    You need to place this code in the VBA module of the worksheet where your data is located. To do this, follow these steps:

    1. Right-click on the sheet tab.
    2. Select "View Code" from the context menu.
    3. In the VBA editor, paste the above code into the code window.

    This code uses the Worksheet_Change event 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.

  3. 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

  4. 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:AB18
    

    Please replace the previous line I provided with this one in the code.

  5. User

    The VBA works brilliantly

  6. 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.