Running a Worksheet_change function when closing the workbook-CodePudding

I want to run a Worksheet_change function that will collect the cell references of any changed cells into an array of "Cells" objects but I keep getting the error "Type mismatch". This is what i've got so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arArray(1 To 70) As Range
Dim K As Integer
K = 1
    For i = 1 To 70
        For j = 2 To 14
        If Target.Column = j And Target.Row = i Then
           Set arArray(K) = Target.Address
        K = K   1
        End If
        Next j
    Next i
End Sub

Currently the code looks for any changes within the grid B1 to N70 and stores the changed cell if a change has occurred to a cell within that grid.

Any help would be greatly appreciated.

CodePudding user response：

Right now, your code is set to look over many cells every time any cell changes. Based on your initial description, I'm sure that this is not what you really want. In the following code, Worksheet_Change keeps track of each cell that gets changed in B1:N70 by putting its address in a collection named "changed_cells". While "show_changes" prints the address of the cells that got changed to the immediate window.

Option Explicit
Dim changed_cells As New Collection

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("B1:N70"), Target) Is Nothing Then
    changed_cells.Add Target.Address(False, False)
  End If
End Sub

Private Sub show_changes()
    Dim x As Long
    For x = 1 To changed_cells.Count
       Debug.Print changed_cells(x)
    Next
End Sub

Note: If the immediate window is not visible, press ctrl g to see the ouptut

CodePudding user response：

You declared an array of Range objects at the top and the Target.Address property returns a string.

Your line

Set arArray(K) = Target.Address

Should be

Set arArray(K) = Range(Target.Address)