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

I want a vba code that will perform the following functions on cell change; On entering…

  1. User

    I want a vba code that will perform the following functions on cell change;

    On entering a value 'b' anywhere in range AC3:AG18, determine the row offset position of value 'b' relative to the right of range AB3:AB18. On the same row as value 'b' determine the value in range AB3:AB18 then search the range D3:D18, L3:L18, T3:T18 for the exact value. Once the value has been found in the range D3:D18, L3:L18, T3:T18, append the value 'y' with the same offset poistion of value 'b'

  2. Assistant

    Here's the VBA code that can perform the functions you described:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngSearch As Range
        Dim rngData As Range
        Dim rngOffset As Range
        Dim cell As Range
        
        Set rngData = Range("AB3:AB18")
        Set rngSearch = Union(Range("D3:D18"), Range("L3:L18"), Range("T3:T18"))
        
        If Not Intersect(Target, Range("AC3:AG18")) Is Nothing Then
            Application.EnableEvents = False
            
            For Each cell In Target
                If cell.Value = "b" Then
                    Set rngOffset = rngData.Cells(cell.Row - rngData.Row + 1).Offset(0, rngData.Columns.Count)
                    
                    For Each c In rngSearch
                        If c.Value = rngOffset.Value Then
                            c.Offset(0, rngOffset.Column - rngSearch.Column) = "y"
                            Exit For
                        End If
                    Next c
                End If
            Next cell
            
            Application.EnableEvents = True
        End If
    End Sub
    

    This code should be placed in the code module for the worksheet where you want the code to be triggered. To do this:

    1. Right-click on the sheet tab.
    2. Select "View Code" from the context menu.
    3. Paste the code into the code window that appears.

    Now, whenever a value 'b' is entered in the range AC3:AG18, the code will determine the relative row offset of 'b' in the range AB3:AB18. It will then search the ranges D3:D18, L3:L18, and T3:T18 for an exact match of the value in AB3:AB18. If a match is found, it will append the value 'y' to the same offset position of 'b' in the matching range.

    Please note that this code assumes that the ranges AB3:AB18, D3:D18, L3:L18, and T3:T18 are located in the same worksheet as the code. Also, keep in mind that multiple 'b' values entered simultaneously may cause the code to append 'y' to the last matching value found.

  3. User

    code is not giving errors but it is also not doing any thing

  4. Assistant

    Apologies for that. It seems there was an oversight in the code. Please try the updated code below:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngSearch As Range
        Dim rngData As Range
        Dim rngOffset As Range
        Dim cell As Range
        
        Set rngData = Range("AB3:AB18")
        Set rngSearch = Union(Range("D3:D18"), Range("L3:L18"), Range("T3:T18"))
        
        If Not Intersect(Target, Range("AC3:AG18")) Is Nothing Then
            Application.EnableEvents = False
            
            For Each cell In Target
                If cell.Value = "b" Then
                    Set rngOffset = rngData.Find(What:=cell.Offset(0, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
                    
                    If Not rngOffset Is Nothing Then
                        rngSearch.Cells(cell.Row - rngData.Row + 1, rngOffset.Column - rngData.Column + 1).Value = "y"
                    End If
                End If
            Next cell
            
            Application.EnableEvents = True
        End If
    End Sub
    

    This updated code should now correctly perform the desired functions. Specifically, it will:

    1. Check if any cells in the range AC3:AG18 were changed.
    2. If a cell contains the value "b", it will find the corresponding value in the range AB3:AB18.
    3. It will then search for this value in the ranges D3:D18, L3:L18, and T3:T18.
    4. If a match is found, it will append the value 'y' to the corresponding offset cell.

    Please make sure to replace your current code with this updated version. Let me know if you encounter any further issues.

  5. User

    the code does not appear to be doing anything. Are there checks we can add to the code, for example a check to see if it finds the mact in range D3:D18, L3:L18, and T3:T18

  6. Assistant

    Apologies for the confusion. I realize that the previous code doesn't include a check to verify if the value has been found in the search ranges. Here's an updated version of the code that includes the checks:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngSearch As Range
        Dim rngData As Range
        Dim rngOffset As Range
        Dim cell As Range
        Dim foundCell As Range
        
        Set rngData = Range("AB3:AB18")
        Set rngSearch = Union(Range("D3:D18"), Range("L3:L18"), Range("T3:T18"))
        
        If Not Intersect(Target, Range("AC3:AG18")) Is Nothing Then
            Application.EnableEvents = False
            
            For Each cell In Target
                If cell.Value = "b" Then
                    Set rngOffset = rngData.Find(What:=cell.Offset(0, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
                    
                    If Not rngOffset Is Nothing Then
                        Set foundCell = rngSearch.Find(What:=rngOffset.Value, LookIn:=xlValues, LookAt:=xlWhole)
                        If Not foundCell Is Nothing Then
                            foundCell.Offset(0, rngOffset.Column - rngSearch.Column) = "y"
                        End If
                    End If
                End If
            Next cell
            
            Application.EnableEvents = True
        End If
    End Sub
    

    This updated code adds an additional check to verify if the value has been found in the search ranges (D3:D18, L3:L18, T3:T18). If the value is found, it will append 'y' to the corresponding offset cell.

    Please replace your existing code with this updated version. Let me know if you have any further questions or issues.

  7. User

    code does not appear to be doing anything

  8. Assistant

    Apologies for the confusion. To troubleshoot the issue, let's try adding some debugging statements to the code. Please replace your current code with the following updated version:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngSearch As Range
        Dim rngData As Range
        Dim rngOffset As Range
        Dim cell As Range
        Dim foundCell As Range
        
        Set rngData = Range("AB3:AB18")
        Set rngSearch = Union(Range("D3:D18"), Range("L3:L18"), Range("T3:T18"))
        
        If Not Intersect(Target, Range("AC3:AG18")) Is Nothing Then
            Application.EnableEvents = False
            
            For Each cell In Target
                If cell.Value = "b" Then
                    Set rngOffset = rngData.Find(What:=cell.Offset(0, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
                    
                    If Not rngOffset Is Nothing Then
                        Set foundCell = rngSearch.Find(What:=rngOffset.Value, LookIn:=xlValues, LookAt:=xlWhole)
                        If Not foundCell Is Nothing Then
                            foundCell.Offset(0, rngOffset.Column - rngSearch.Column).Value = "y"
                            MsgBox "Value 'y' appended to cell " & foundCell.Offset(0, rngOffset.Column - rngSearch.Column).Address
                        Else
                            MsgBox "Value '" & rngOffset.Value & "' not found in search ranges"
                        End If
                    End If
                End If
            Next cell
            
            Application.EnableEvents = True
        End If
    End Sub
    

    This updated code includes MsgBox statements to display messages when appending 'y' or if the value is not found in the search ranges. Whenever 'b' is entered in the range AC3:AG18 and a corresponding match is found, a message box will appear to indicate that 'y' has been appended to the cell.

    Please test the code again and let me know if any error messages or message boxes are displayed. This will help identify the issue and provide a solution.

  9. User

    Set rngData = Range(“AB3:AB18”) is been found in Set rngSearch = Union(Range(“D3:D18”), Range(“L3:L18”), Range(“T3:T18”))

  10. Assistant

    The issue you're encountering is likely due to the use of smart quotes (curly quotes) instead of straight quotes in the code. Make sure to replace the smart quotes with straight quotes. Here's the corrected code snippet:

    Set rngData = Range("AB3:AB18")
    Set rngSearch = Union(Range("D3:D18"), Range("L3:L18"), Range("T3:T18"))
    

    Please update the code with these straight quotes and try running it again. Let me know if you encounter any further issues.

  11. User

    all quotes have been corrected

  12. Assistant

    Apologies for the confusion. In that case, please ensure that the rest of the code, including the rest of the smart quotes, is also corrected. Here's the complete code with all quotes replaced:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngSearch As Range
        Dim rngData As Range
        Dim rngOffset As Range
        Dim cell As Range
        Dim foundCell As Range
        
        Set rngData = Range("AB3:AB18")
        Set rngSearch = Union(Range("D3:D18"), Range("L3:L18"), Range("T3:T18"))
        
        If Not Intersect(Target, Range("AC3:AG18")) Is Nothing Then
            Application.EnableEvents = False
            
            For Each cell In Target
                If cell.Value = "b" Then
                    Set rngOffset = rngData.Find(What:=cell.Offset(0, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
                    
                    If Not rngOffset Is Nothing Then
                        Set foundCell = rngSearch.Find(What:=rngOffset.Value, LookIn:=xlValues, LookAt:=xlWhole)
                        If Not foundCell Is Nothing Then
                            foundCell.Offset(0, rngOffset.Column - rngSearch.Column).Value = "y"
                            MsgBox "Value 'y' appended to cell " & foundCell.Offset(0, rngOffset.Column - rngSearch.Column).Address, vbInformation
                        Else
                            MsgBox "Value '" & rngOffset.Value & "' not found in search ranges", vbExclamation
                        End If
                    End If
                End If
            Next cell
            
            Application.EnableEvents = True
        End If
    End Sub
    

    With all the quotes corrected, please test the code again and let me know if the issue persists or if any error messages are displayed.