Dynamic Array - Data Erased

Pjam

Board Regular
Joined
Jun 4, 2008
Messages
139
The code i have below erases the data when it goes to the next i , how do i store this information to use a later time?

Code:
Option Base 1
Sub test()
Dim leasenumber  As Variant
Dim leaseitems   As Variant
Dim l            As Variant

For i = 1 To 10
leasenumber = Cells(i, 1)
leaseitems = Cells(i, 2)
l = Cells(i, 3)
ReDim myarray(leasenumber, l) As Variant

  myarray(leasenumber, l) = leaseitems
ReDim myarray(leasenumber, l) As Variant
'ReDim Preserve myarray(i, i + i)
Next
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You cannot Redim an array without erasing it. You can Redim Preserve an array, but I believe it is only the last dimension that can be resized.

This code looks awfully tortuous. You have a variable sized array, and are setting the second dimension based on a variable, which is coming from a cell, which is inside a loop. I can barely follow it ... but if the data is in the cells already, it may be simpler to use the worksheet as your data store instead of an array. In any case, you seem to know that there's 3 columns, so why use a variable second dimension?

ξ
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,583
Messages
6,179,681
Members
452,937
Latest member
Bhg1984

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
Back
Top