This is not an answer but advice that is too complicated for a comment.
If the array formulae were in place before the column was deleted, I would expect Excel to adjust them as necessary if it can. I am assuming the worksheet was not as you expected when your macro was run.
I have been burnt by someone changing a worksheet before running a macro of mine. On that occasion, the change was quite subtle and it appeared the macro worked. It was sometime before they noticed that the data was being corrupted.
Now the first stage of a macro of mine is to check assumptions about worksheets I do not control but on which the macro depends.
If there is a way - other than checking individual values - to confirm a column only contains numeric values, say, I do not know it. I will check invidual values if I am worried enough but you can check the number format which may be an adequate proxy. I will certainly check the values of column headings and table headings.
I created a new workbook and set worksheet "Sheet2" to:
The macro below produced the following output:
The number formats within Range E1:E16 are 0.00
The number formats within Range E1:E17 are not all the same
Check Values failure: Worksheet Sheet4 not found
Check Values failure: Cell E2 has a value of [2] but I was expecting [5]
You can see it is fairly easy to check for unexpected formatting, missing worksheets and incorrect cell values. I have developed a set of checking functions for myself which are not suitable for sharing but you can see what is possible.
Option Explicit
Sub Test()
Dim ErrMsg As String
Dim Rng As Range
Dim NF As Variant
With Worksheets("Sheet2")
Set Rng = .Range("E1:E16")
NF = Rng.NumberFormat
If IsNull(NF) Then
Debug.Print "The number formats within Range " & _
Replace(Rng.Address, "$", "") & " are not all the same"
Else
Debug.Print "The number formats within Range " & _
Replace(Rng.Address, "$", "") & " are " & NF
End If
Set Rng = .Range("E1:E17")
NF = Rng.NumberFormat
If IsNull(NF) Then
Debug.Print "The number formats within Range " & _
Replace(Rng.Address, "$", "") & " are not all the same"
Else
Debug.Print "The number formats within Range " & _
Replace(Rng.Address, "$", "") & " are " & NF
End If
End With
Call CheckValues(ThisWorkbook.Name, "Sheet2", ErrMsg, _
"C2", "Date", "C5", "Name", "C9", "Id")
If ErrMsg <> "" Then
Debug.Print "Check Values failure: " & ErrMsg
End If
Call CheckValues(ThisWorkbook.Name, "Sheet4", ErrMsg, _
"C2", "Date", "C5", "Name", "C9", "Id")
If ErrMsg <> "" Then
Debug.Print "Check Values failure: " & ErrMsg
End If
Call CheckValues(ThisWorkbook.Name, "Sheet2", ErrMsg, _
"E1", 1, "E2", 5)
If ErrMsg <> "" Then
Debug.Print "Check Values failure: " & ErrMsg
End If
End Sub
Sub CheckValues(ByVal WbkName As String, ByVal WshtName As String, _
ByRef ErrMsg As String, ParamArray CellDtl() As Variant)
' If the specified cells have the expected values, ErrMsg will be empty
' on return. Otherwise ErrMsg will report the first cell with an
' unexpected value.
' WbkName The name of an open workbook.
' WshtName The name of an worksheet within the workbook.
' CellDtl Must contain an even number of values. The first value
' of each paid must be a cell address such as "C1". The
' second value must be the expected value of that cell.
' for exampe ... "B1", Name", "C1", "Date", ... indicates
' that cell B1 should have a value of "Name" and cell C1
' should have a value of "Date".
Dim Found As Boolean
Dim InxCD As Long
Dim InxWbk As Long
Dim InxWsht As Long
Found = False
For InxWbk = 1 To Workbooks.Count
If WbkName = Workbooks(InxWbk).Name Then
Found = True
Exit For
End If
Next
If Not Found Then
ErrMsg = "Workbook " & WbkName & " is not open"
Exit Sub
End If
With Workbooks(WbkName)
Found = False
For InxWsht = 1 To .Worksheets.Count
If WshtName = .Worksheets(InxWsht).Name Then
Found = True
Exit For
End If
Next
If Not Found Then
ErrMsg = "Worksheet " & WshtName & " not found"
Exit Sub
End If
With .Worksheets(WshtName)
For InxCD = 0 To UBound(CellDtl) Step 2
If .Range(CellDtl(InxCD)).Value <> CellDtl(InxCD + 1) Then
ErrMsg = "Cell " & CellDtl(InxCD) & " has a value of [" & _
.Range(CellDtl(InxCD)).Value & "] but I was expecting [" & _
CellDtl(InxCD + 1) & "]"
Exit Sub
End If
Next
End With
End With
' All value match
ErrMsg = ""
End Sub
