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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,690
Office Version
  1. 2013
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,108,959
Messages
5,525,901
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top