Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

I want to write a VBA code. In my excel spread sheet, I have 4 ranges. D3:D18, L3:L18,…

  1. User

    I want to write a VBA code. In my excel spread sheet, I have 4 ranges. D3:D18, L3:L18, T3:T18, AB3:AB18. After entering a value in a cell AB3:AB18, I will also enter a value 'y' offset to the right on the same row. My offset 'y' value will be between offset 1 to offset 4 of the cell in the AB3:AB18 range. When I enter a 'y' value offset of the cell in the AB3:AB18 range, I need the vba code to search the other ranges D3:D18, L3:L18, T3:T18, and locate the exact value that is in the AB3:AB18 range and enter a 'y' value in the same offset position to the match found

  2. Assistant

    You can use the following VBA code to achieve your requirement:

    Sub UpdateOffsetValue()
        Dim searchRange As Range
        Dim offsetRange As Range
        Dim searchValue As Variant
        Dim offsetValue As Variant
        Dim rng As Range
        
        ' Set the search range
        Set searchRange = Range("D3:D18,L3:L18,T3:T18")
        
        ' Set the offset range
        Set offsetRange = Range("AB3:AB18")
        
        ' Get the entered offset value in the active cell
        offsetValue = ActiveCell.Value
        
        ' Check if the offset value is between 1 and 4
        If offsetValue >= 1 And offsetValue <= 4 Then
            ' Get the search value in the active cell
            searchValue = ActiveCell.Offset(0, -offsetValue).Value
            
            ' Loop through each cell in the search range
            For Each rng In searchRange
                ' Check if the search value matches the value in the range
                If rng.Value = searchValue Then
                    ' Enter the offset value in the same position in the offset range
                    offsetRange.Cells(rng.Row - offsetRange.Row + 1).Offset(0, offsetValue).Value = "y"
                    Exit For
                End If
            Next rng
        End If
            
    End Sub
    

    To use the code, follow these steps:

    1. Press "Alt + F11" to open the VBA editor.
    2. Insert a new module by going to "Insert > Module".
    3. Copy and paste the code into the new module.
    4. Close the VBA editor.
    5. Save the workbook as a macro-enabled (.xlsm) file.
    6. Enter a value in the cell in the AB3:AB18 range.
    7. Enter the desired offset value in any of the cells adjacent to the entered value (to the left) in the AB3:AB18 range.
    8. Activate any cell in the AB3:AB18 range.
    9. Press "Alt + F8" to open the "Macro" dialog box.
    10. Select the "UpdateOffsetValue" macro from the list and click "Run".
    11. The macro will search the other ranges (D3:D18, L3:L18, T3:T18) and find the matching value in the AB3:AB18 range.
    12. It will then enter a 'y' value in the same offset position in the match found.

    Make sure to adjust the range references (D3:D18, L3:L18, T3:T18, AB3:AB18) in the code to match your actual range on the worksheet.

  3. User

    The code is not working. Can you modify this code to work: 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 - 1 ' Calculate the offset

    Dim resultOffset As Range Set resultOffset = resultCell.Offset(, offsetVal)

    If resultOffset.Value = "x" 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

  4. Assistant

    Apologies for the confusion earlier. You can try using the updated code below, which includes modifications to make it work properly:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngChange As Range
        Dim rngValues As Range
        Dim rngOffset As Range
        Dim lookupValue As Variant
        Dim resultRng As Range
        Dim resultCell As Range
        Dim offsetVal As Long
        Dim resultOffset As Range
        Dim cell As Range
        Dim rowNum As Long
        
        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)
        resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))
        
        For Each cell In rngChange
            If cell.Value = "y" Then
                rowNum = cell.Row - rngChange.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18
                
                Set resultCell = resultRng.Find(lookupValue, LookIn:=xlValues)
                If Not resultCell Is Nothing Then
                    offsetVal = rngChange.Columns(1).Column - rngOffset.Columns(1).Column - 1 ' Calculate the offset
                    Set resultOffset = resultCell.Offset(, offsetVal)
                    
                    If resultOffset.Value = "x" 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
    

    Make sure to place this code in the worksheet module where your data is located. To do this:

    1. Press "Alt + F11" to open the VBA editor.
    2. Find the worksheet with the data in the "Project Explorer" pane (usually on the left side of the VBA editor).
    3. Double-click on the worksheet to open its code window.
    4. Paste the updated code into the code window.
    5. Close the VBA editor.

    Now, whenever you enter a "y" value in any of the cells in the range AC3:AG18, the code will search for a matching value in the AB3:AB18 range. If a match is found, it will look for the same offset position in the ranges D3:D18, L3:L18, T3:T18. If the offset position in the matching range is "x", it will update it to "y". Otherwise, it will display a message box indicating that the current bay is not free on that day.

  5. User

    Object variable or With block variable not set on this line of code: resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))

  6. Assistant

    Apologies for the oversight. The issue is with the line resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18")) as it should be declared as a Set statement since it's a range assignment. Here's the corrected code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngChange As Range
        Dim rngValues As Range
        Dim rngOffset As Range
        Dim lookupValue As Variant
        Dim resultRng As Range
        Dim resultCell As Range
        Dim offsetVal As Long
        Dim resultOffset As Range
        Dim cell As Range
        Dim rowNum As Long
        
        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)
        Set resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))
        
        For Each cell In rngChange
            If cell.Value = "y" Then
                rowNum = cell.Row - rngChange.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18
                
                Set resultCell = resultRng.Find(lookupValue, LookIn:=xlValues)
                If Not resultCell Is Nothing Then
                    offsetVal = rngChange.Columns(1).Column - rngOffset.Columns(1).Column - 1 ' Calculate the offset
                    Set resultOffset = resultCell.Offset(, offsetVal)
                    
                    If resultOffset.Value = "x" 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
    

    Please try running the code again, and it should work correctly without any errors. Let me know if you encounter any further issues.

  7. User

    Private Sub Worksheet_Change(ByVal Target As Range) Dim rngChange As Range Dim rngValues As Range Dim rngOffset As Range Dim lookupValue As Variant Dim resultRng As Range Dim resultCell As Range Dim offsetVal As Long Dim resultOffset As Range Dim cell As Range Dim rowNum As Long

    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)
    Set resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))
    
    For Each cell In rngChange
        If cell.Value = "y" Then
            rowNum = cell.Row - rngChange.Rows(1).Row + 1 ' Calculate the relative row number
            lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18
            
            Set resultCell = resultRng.Find(lookupValue, LookIn:=xlValues)
            If Not resultCell Is Nothing Then
                offsetVal = rngChange.Columns(1).Column - rngOffset.Columns(1).Column - 1 ' Calculate the offset
                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........ This code works perfectly. However, can you add this action: after the message box, I want the 'y' that was rejected to return back to ""

  8. Assistant

    Certainly! To restore the rejected "y" value to empty ("") after displaying the message box, you can modify the code as follows:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngChange As Range
        Dim rngValues As Range
        Dim rngOffset As Range
        Dim lookupValue As Variant
        Dim resultRng As Range
        Dim resultCell As Range
        Dim offsetVal As Long
        Dim resultOffset As Range
        Dim cell As Range
        Dim rowNum As Long
        
        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)
        Set resultRng = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))
        
        For Each cell In rngChange
            If cell.Value = "y" Then
                rowNum = cell.Row - rngChange.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValue = rngValues.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18
                
                Set resultCell = resultRng.Find(lookupValue, LookIn:=xlValues)
                If Not resultCell Is Nothing Then
                    offsetVal = rngChange.Columns(1).Column - rngOffset.Columns(1).Column - 1 ' Calculate the offset
                    Set resultOffset = resultCell.Offset(, offsetVal)
                    
                    If resultOffset.Value = "z" Then
                        resultOffset.Value = "y"
                    Else
                        MsgBox "Current Bay is not Free on this day"
                        cell.Value = "" ' Reset the rejected "y" value to empty
                        Exit Sub
                    End If
                End If
            End If
        Next cell
        
        'Application.EnableEvents = True
    End Sub
    

    With this modification, if the "y" value is rejected and the message box is displayed, the corresponding cell in the AC3:AG18 range will be reset to empty (""), effectively removing the rejected "y" value.

    Please give it a try and let me know if it meets your expectations!

  9. User

    Can you help me correct this: Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngChange As Range
    
    Set rngChange = Intersect(Target, Me.Range("AC3:AG18"))
    If rngChange Is Nothing Then Exit Sub
    Else: Call ThisWeek
    End If
    
    Set rngChange = Intersect(Target, Me.Range("AC22:AG37"))
    If rngChange Is Nothing Then Exit Sub
    Else: CallNextWeek
    End If
    
    End Sub
    
  10. Assistant

    Certainly! Here's the corrected code:

    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
            ThisWeek
            Exit Sub
        End If
        
        Set rngChange = Intersect(Target, Me.Range("AC22:AG37"))
        If Not rngChange Is Nothing Then
            Call NextWeek
        End If
    End Sub
    

    In this code, I have added two separate blocks of code for each range (AC3:AG18 and AC22:AG37). If the changed range falls within AC3:AG18, it will call the ThisWeek subroutine. Similarly, if the changed range falls within AC22:AG37, it will call the NextWeek subroutine.

    Please make sure you have the ThisWeek and NextWeek subroutines defined elsewhere in your code with the desired functionality.

  11. User

    Sub ThisWeek() 'Application.EnableEvents = False

    Dim rngChangeTW As Range
    Dim rngValuesTW As Range
    Dim rngOffsetTW As Range
    Dim lookupValueTW As Variant
    Dim resultRngTW As Range
    Dim resultcellTW As Range
    Dim offsetValTW As Long
    Dim resultOffsetTW As Range
    Dim cell As Range
    Dim rowNum As Long
    
    Set rngValuesTW = Me.Range("AB3:AB18")
    Set rngOffsetTW = rngValuesTW.Offset(, -1) ' Offset by 1 column to the left (AB -> AA)
    Set resultRngTW = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))
    
    For Each cell In rngChangeTW
        If cell.Value = "y" Then
            rowNum = cell.Row - rngChangeTW.Rows(1).Row + 1 ' Calculate the relative row number
            lookupValueTW = rngValuesTW.Cells(rowNum, 1).Value ' Get the corresponding value from AB3:AB18
            
            Set resultcellTW = resultRngTW.Find(lookupValueTW, LookIn:=xlValues)
            If Not resultcellTW Is Nothing Then
                offsetValTW = rngChangeTW.Columns(1).Column - rngOffsetTW.Columns(1).Column - 1 ' Calculate the offset
                Set resultOffsetTW = resultcellTW.Offset(, offsetValTW)
                
                If resultOffsetTW.Value = "z" Then
                    resultOffsetTW.Value = "y"
                Else
                    MsgBox "Current Bay is not Free on this day"
                    cell.Value = "" ' Reset the rejected "y" value to empty
                    Exit Sub
                End If
            End If
        End If
    Next cell
    
    'Application.EnableEvents = True
    End Sub.....
    

    For the code above, I am getting an error, Object Required, for this line of code; For Each cell In rngChangeTW

  12. Assistant

    The rngChangeTW variable in the ThisWeek subroutine is not being set with a range, resulting in the "Object Required" error. Here's the modified code to fix the error:

    Sub ThisWeek()
        'Application.EnableEvents = False
    
        Dim rngChangeTW As Range
        Dim rngValuesTW As Range
        Dim rngOffsetTW As Range
        Dim lookupValueTW As Variant
        Dim resultRngTW As Range
        Dim resultcellTW As Range
        Dim offsetValTW As Long
        Dim resultOffset
    

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