Subscript out of range when trying to re-dimension an array

[TheRedCardinal]

As is so often the case, what I thought would be a simple project has got complicated.

My needs are these:

1. Load the data of a table into an array in VBA - DataArray
2. Load the data of another table into an array - the table data may or may not be blank at the time - AmendmentsArray
3. Perform a series of checks on the data in Data Array in a loop
4. Where a test is met, update the data in the DataArray with the instructions for that test (example below)
5. Add a new line to the AmendmentsArray, containing details of the amended line.
6. Repeat for all rows of the DataArray
7. Repeat for several tests
8. Output DataArray back into the table (will be the same size as imported)
9. Output AmendmentsArray into the table (will probably be different size, and had to be transposed (see below)

Here is my code:

Sub PopulateAmendments()

'Test macro to develop tool to populate table with amendments
'Will load data table into array, perform tests
'Every time test is met, will update amendments table

Set Wbk1 = ThisWorkbook
Set WS1 = Wbk1.Sheets("Data Sheet")
Set WS2 = Wbk1.Sheets("Amendments Made")

Dim DataArray As Variant, AmendmentArray As Variant
Dim AmendCounter As Long, Counter As Long

DataArray = WS1.ListObjects("DataTable").DataBodyRange.Value

If WS2.ListObjects("AmndTable").DataBodyRange Is Nothing Then
    
    AmendCounter = 1
    ReDim AmendmentArray(1 To 4, 1 To 1)

Else

    AmendmentArray = WS2.ListObjects("AmndTable").DataBodyRange.Value
    AmendCounter = WS2.ListObjects("AmndTable").DataBodyRange.Rows.Count + 1

End If

'Theoretical Test 1
'Will pass for row 1 and 2, and not for remainder

For Counter = 1 To UBound(DataArray)

    If DataArray(Counter, 5) = 123456789 Then
        
'Add details to the array

        AmendmentArray(1, AmendCounter) = AmendCounter
        AmendmentArray(2, AmendCounter) = DataArray(Counter, 3)
        AmendmentArray(3, AmendCounter) = "Sequential Number Test"
        AmendmentArray(4, AmendCounter) = "The number has been changed from 123456789 to 987654321"
        
'Increase size of AmendmentArray
    
        AmendCounter = AmendCounter + 1
        ReDim Preserve AmendmentArray(1 To 4, 1 To AmendCounter)

'Amend Original Data
        
        DataArray(Counter, 5) = 87654321
        
    End If

Next Counter

WS1.ListObjects("DataTable").DataBodyRange.Value = DataArray
WS2.ListObjects("AmndTable").DataBodyRange.Value = WorksheetFunction.Transpose(AmendmentArray)

Things started to go wrong when I realised that I had to keep changing the size of the Amendments array to put new lines in; and using ReDim Preserve required me to shift the shape of the array to allow me to resize the final dimension of the array.

Now when I want to write the array back, I can't figure out how to do it - I thought the Transpose function would be the one but I am getting an error of "Object Variable or With block variable not set at that final line"

So my 2 questions are:

1. Is there a better way to do this than the way I've ended up with?
2. If not, how can I fix that final stage to output array into the table?

In my table, the column headers are now array entries 1,1; 2,1; 3;1, etc, whereas naturally they would be 1,1; 1,2; 1,3.

Thanks all!

 

[TheRedCardinal]

Also as I re-read that post, I now realise I have a different problem.

The macro tests if the amendments table on WS2 has anything in it, if it's blank we proceed as normal; but if it's not blank, it loads the existing amendments in so that they can be added to. I now realise that I need to transpose this on the way into VBA as well.