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

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
169
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:

VBA 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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
169
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,780
Members
416,939
Latest member
Rajakumaran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top