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

Sub ThisWeek() 'Application.EnableEvents = False Dim rngChangeTW As Range Dim…

  1. 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 cellTW As Range
    Dim rowNumTW As Long
    
    Set rngValuesTW = Me.Range("AB3:AB18")
    Set rngChangeTW = Me.Range("AC3:AG18")
    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 cellTW In rngChangeTW
        If cellTW.Value = "y" Then
            rowNumTW = cellTW.Row - rngChangeTW.Rows(1).Row + 1 ' Calculate the relative row number
            lookupValueTW = rngValuesTW.Cells(rowNumTW, 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"
                    cellTW.Value = "" ' Reset the rejected "y" value to empty
                    Exit Sub
                End If
            End If
        End If
    Next cellTW
    
    'Application.EnableEvents = True
    End Sub
    

    In this code, the OffsetValTW does not match the offset position of rngValuesTW. When I enter 33 in AB3, and 'y' in AE3 of the same row, the code correctly finds the exact AB3 match in T12, but it does not enter 'y' into the correct offset position which should be W12. It always enters the 'y' into U12

  2. Assistant

    To update the offset position in the code, you need to change the calculation of offsetValTW. Instead of subtracting 1 from rngOffsetTW.Columns(1).Column, subtract 1 from rngOffsetTW.Columns(rngOffsetTW.Columns.Count).Column.

    Updated code:

    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 cellTW As Range
        Dim rowNumTW As Long
        
        Set rngValuesTW = Me.Range("AB3:AB18")
        Set rngChangeTW = Me.Range("AC3:AG18")
        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 cellTW In rngChangeTW
            If cellTW.Value = "y" Then
                rowNumTW = cellTW.Row - rngChangeTW.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValueTW = rngValuesTW.Cells(rowNumTW, 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(rngOffsetTW.Columns.Count).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"
                        cellTW.Value = "" ' Reset the rejected "y" value to empty
                        Exit Sub
                    End If
                End If
            End If
        Next cellTW
        
        'Application.EnableEvents = True
    End Sub
    

    With this change, the code should correctly enter 'y' into the offset position W12 when 'y' is entered in AE3 and the corresponding value in AB3 matches T12.

  3. User

    It is still not putting the 'y' in the correct offset position

  4. Assistant

    I apologize for the confusion. In order to determine the correct offset position, you need to calculate the column offset using the difference between the column indexes of rngChangeTW and rngValuesTW. Here's the updated code:

    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 cellTW As Range
        Dim rowNumTW As Long
        
        Set rngValuesTW = Me.Range("AB3:AB18")
        Set rngChangeTW = Me.Range("AC3:AG18")
        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 cellTW In rngChangeTW
            If cellTW.Value = "y" Then
                rowNumTW = cellTW.Row - rngChangeTW.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValueTW = rngValuesTW.Cells(rowNumTW, 1).Value ' Get the corresponding value from AB3:AB18
                
                Set resultcellTW = resultRngTW.Find(lookupValueTW, LookIn:=xlValues)
                If Not resultcellTW Is Nothing Then
                    offsetValTW = cellTW.Column - rngChangeTW.Columns(1).Column ' 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"
                        cellTW.Value = "" ' Reset the rejected "y" value to empty
                        Exit Sub
                    End If
                End If
            End If
        Next cellTW
        
        'Application.EnableEvents = True
    End Sub
    

    With this updated code, the offsetValTW variable is calculated as the difference between the column index of the current cell in rngChangeTW and the first column in rngChangeTW. This will ensure that the 'y' value is entered into the correct offset column in the result range.

  5. User

    the change in the code is puting the 'y' 1 cell to the left of the position it should be

  6. Assistant

    Apologies for the confusion. In order to correctly calculate the offset between the rngChangeTW range and the resultRngTW range, you need to account for the starting column of resultRngTW.

    Here's the updated code:

    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 cellTW As Range
        Dim rowNumTW As Long
        
        Set rngValuesTW = Me.Range("AB3:AB18")
        Set rngChangeTW = Me.Range("AC3:AG18")
        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 cellTW In rngChangeTW
            If cellTW.Value = "y" Then
                rowNumTW = cellTW.Row - rngChangeTW.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValueTW = rngValuesTW.Cells(rowNumTW, 1).Value ' Get the corresponding value from AB3:AB18
                
                Set resultcellTW = resultRngTW.Find(lookupValueTW, LookIn:=xlValues)
                If Not resultcellTW Is Nothing Then
                    offsetValTW = cellTW.Column - rngChangeTW.Columns(1).Column + resultRngTW.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"
                        cellTW.Value = "" ' Reset the rejected "y" value to empty
                        Exit Sub
                    End If
                End If
            End If
        Next cellTW
        
        'Application.EnableEvents = True
    End Sub
    

    With this change, the offsetValTW variable is calculated as the difference between the column index of the current cell in rngChangeTW and the first column in rngChangeTW, along with accounting for the starting column index of resultRngTW. This will ensure that the 'y' value is entered into the correct offset column in the result range.

  7. User

    the code change is not working

  8. Assistant

    Apologies for the confusion. In order to correctly calculate the offset between the rngChangeTW range and the resultRngTW range, you need to account for the starting column of resultRngTW.

    Here's the updated code:

    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 cellTW As Range
        Dim rowNumTW As Long
        
        Set rngValuesTW = Me.Range("AB3:AB18")
        Set rngChangeTW = Me.Range("AC3:AG18")
        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 cellTW In rngChangeTW
            If cellTW.Value = "y" Then
                rowNumTW = cellTW.Row - rngChangeTW.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValueTW = rngValuesTW.Cells(rowNumTW, 1).Value ' Get the corresponding value from AB3:AB18
                
                Set resultcellTW = resultRngTW.Find(lookupValueTW, LookIn:=xlValues)
                If Not resultcellTW Is Nothing Then
                    offsetValTW = cellTW.Column + resultRngTW.Column - rngChangeTW.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"
                        cellTW.Value = "" ' Reset the rejected "y" value to empty
                        Exit Sub
                    End If
                End If
            End If
        Next cellTW
        
        'Application.EnableEvents = True
    End Sub
    

    With this change, the offsetValTW variable is calculated as the difference between the column index of the current cell in rngChangeTW and the first column in rngChangeTW, along with accounting for the starting column index of resultRngTW. This will ensure that the 'y' value is entered into the correct offset column in the result range.

  9. User

    the change in the code is putting the ‘y’ 2 cells to the right of the position it should be

  10. Assistant

    Apologies for the previous incorrect code. Here is the corrected version:

    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 cellTW As Range
        Dim rowNumTW As Long
        
        Set rngValuesTW = Me.Range("AB3:AB18")
        Set rngChangeTW = Me.Range("AC3:AG18")
        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 cellTW In rngChangeTW
            If cellTW.Value = "y" Then
                rowNumTW = cellTW.Row - rngChangeTW.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValueTW = rngValuesTW.Cells(rowNumTW, 1).Value ' Get the corresponding value from AB3:AB18
                
                Set resultcellTW = resultRngTW.Find(lookupValueTW, LookIn:=xlValues)
                If Not resultcellTW Is Nothing Then
                    offsetValTW = resultcellTW.Column - resultRngTW.Columns(1).Column + rngChangeTW.Cells(rowNumTW, 1).Column - rngValuesTW.Columns(1).Column ' 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"
                        cellTW.Value = "" ' Reset the rejected "y" value to empty
                        Exit Sub
                    End If
                End If
            End If
        Next cellTW
        
        'Application.EnableEvents = True
    End Sub
    

    With this corrected version, the offsetValTW variable is calculated as the difference between the column index of the resultcellTW and the first column in resultRngTW, plus the difference between the column index of the corresponding cell in rngChangeTW and the first column in rngValuesTW. This calculation ensures that the 'y' value is entered into the correct offset column in the result range.

  11. User

    the code is not working. I believe that this line of code is wrong; Set rngOffsetTW = rngValuesTW.Offset(, -1) ' Offset by 1 column to the left (AB -> AA).... because the rngOffsetTW cannot be determined until the value 'y' has been entered. The value 'y' will be in the same row corresponding to AB, but the 'y' value may be in any of the columns AC, AD, AE, AF, or AG

  12. Assistant

    Apologies for the confusion. You are correct that the rngOffsetTW cannot be determined before the y value has been entered.

    To address this, you can calculate the rngOffsetTW within the For Each loop, based on the current row that has the y value. Here's the updated code:

    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 cellTW As Range
        Dim rowNumTW As Long
        
        Set rngValuesTW = Me.Range("AB3:AB18")
        Set rngChangeTW = Me.Range("AC3:AG18")
        Set resultRngTW = Union(Me.Range("D3:D18"), Me.Range("L3:L18"), Me.Range("T3:T18"))
        
        For Each cellTW In rngChangeTW
            If cellTW.Value = "y" Then
                rowNumTW = cellTW.Row - rngChangeTW.Rows(1).Row + 1 ' Calculate the relative row number
                lookupValueTW = rngValuesTW.Cells(rowNumTW, 1).Value ' Get the corresponding value from AB3:AB18
                
                Set resultcellTW = resultRngTW.Find(lookupValueTW, LookIn:=xlValues)
                If Not resultcellTW Is Nothing Then
                    ' Calculate rngOffsetTW based on the current 'y' value position
                    Set rngOffsetTW = rngValuesTW.Cells(rowNumTW, 1).Offset(, -1)
                    
                    offsetValTW = resultcellTW.Column - resultRngTW.Columns(1).Column + cellTW.Column - rngOffsetTW.Column ' 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"
                        cellTW.Value = "" ' Reset the rejected "y" value to empty
                        Exit Sub
                    End If
                End If
            End If
        Next cellTW
        
        'Application.EnableEvents = True
    End Sub
    

    With this updated code, the rngOffsetTW is calculated within the loop based on the current row that has the y value in rngChangeTW. This ensures that the correct offset range is determined for each y value entered.

  13. User

    the code change is not working

  14. Assistant

    Apologies for the confusion. To dynamically determine the rngOffsetTW range based on the current row with the y value in rngChangeTW, you can update the code as follows:

    Sub ThisWeek()
        'Application.EnableEvents = False
        
        Dim rngChangeTW As Range
        Dim rngValuesTW As Range
        Dim rng