Search code examples
arraysexcelvbasubscript

Subscript out of range error when referencing cells


the goal of my code is to take the old value of a cell, and check it against a new value, if it is entered. And if the old value changes to a new value, then update the date in a cell specified.

The problem with my code is that I cannot seem to find a way to get around this error without my code breaking, thus I am having trouble trying to fix this one line of code. I know my array is out of bounds or something along those lines, but I cannot figure out how to get around it.

Here's my code:

Dim oldValue()

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
oldValue = Me.Range("D4", "D21").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("D4:D21")) Is Nothing Then
    Dim c As Range
    For Each c In Intersect(Target, Me.Range("D4:D21"))
        'Here's where my code is breaking "Subscript out of range error"
        If oldValue(c.Row) <> c.Value Then
            'Update value in column L (8 columns to the right of column D)
            c.Offset(0, 7).Value = Date 'or possibly "= Now()" if you need the time of day that the cell was updated
        End If
    Next
End If
End Sub

Where it's breaking, I have defined that if the old value changes to the new value, then update the date. But it's giving me an error that I cannot find a way to fix.

How can I fix my code to get it within range, any suggestions?

EDIT: I've now fixed my code:

Dim oldValue As Variant

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
'I changed "D4", "D21" to the following:
oldValue = Me.Range("D4:D21").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("D4:D21")) Is Nothing Then
    'Application.EnableEvents = False
    Dim c As Range
    For Each c In Intersect(Target, Me.Range("D4:D21"))
        'Check value against what is stored in "oldValue" (row 4 is in position 1, row 5 in position 2, etc)
        'I also changed the array reference
        If oldValue(c.Row - 3, 1) <> c.Value Then
            'Update value in column L (8 columns to the right of column D)
            c.Offset(0, 7).Value = Date 'or possibly "= Now()" if you need the time of day that the cell was updated
        End If
    Next
    'Application.EnableEvents = True
End If
End Sub

Solution

  • Dim oldValue as Variant
    
    ....
    
    ' oldValue is a 2D array
    ' and there is a shift between c.Row and the index of oldValue 
    If oldValue(c.Row - 3, 1) <> c.Value Then ...