VBA Dynamic Array

Pjam

Board Regular
Joined
Jun 4, 2008
Messages
139
Hello,

I am trying to write code to store data in a column into an array, but the data is not set a static number of rows

I have numbers that represent when the data changes and when i need a new array. for example

column B starting in cells( 2,1) and then in columnM (2,13)
1 RNEW
1 ADJ
1 RNEW
2 RNEW
2 ADJ
2 RNEW
3 ADJ
3 RNEW
3 ADJ

So i would want the the first array to be

myarray(0) = RNEW
myarray(1) = ADJ
myarray(2) = RNEW

then have another array stored
for when column b changes to 2

Heres what i have so far just not sure how to structure it correctly.


Code:
Sub dynamicDataterms()
Dim wsDataInput As Worksheet
Set wsDataInput = ThisWorkbook.Worksheets("Data Input")
Dim myarray() As Integer
Dim xrow As Integer
Dim size As Integer
xrow = 1
size = 1
index = 0
ReDim myarray(size)
 
myarray(index) = wsDataInput.Cells(xrow + 1, 13).Value
If wsDataInput.Cells(xrow + 1, 2).Value <> wsDataInput.Cells(xrow, 2).Value Then
End If

size = size + 1
ReDim Preserve myarray(size)
index = index + 1
xrow = xrow + 1
End Sub
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
I am not understanding completely.

Are you wanting one array that holds all of your data?

Or a group of array variables that hold distinct subsets of the data?
 

Pjam

Board Regular
Joined
Jun 4, 2008
Messages
139
right a group of array variables that hold distinct subsets of the data?
 

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
And how would you know how many array variables you would need? If your list contains, say, 500 subsets of data, you would want to declare 500 array variables? ( I would not.) And if the data doesn't contain 500 subsets, but potentially could, you would still need to declare them so they could be available, just in case. Maybe someone else has a workaround to this, but it seems like you would be reserving a lot of memory space for "just in case".

To me it doesn't sound like something you really want to do...just too labor intensive and messy. It would become very difficult even to keep track of all those arrays, let alone use the data in them reliably. Imagine having to write all the rules for which array to use...:confused:

I am speculating here because I do not see where you want to go, but:

Perhaps if we knew what your eventual goal is? What is it you want to do with the data once you get it in arrays?

Couldn't you just load a single array with your data, then parse it out as needed?
 

Pjam

Board Regular
Joined
Jun 4, 2008
Messages
139
I have a bunch of rules that I need to apply to the text "RNEW" , "Adj" etc in column M , the problem I am having is that the rules apply to the values prior to eachother
fr example if at any time a ":RNEW" comes before an "ADJ" the "ADJ" = Current , I thought the best way to do this is store the information into an array so i can reference all values and write the rules . maybe this isnt the best way to do it any pointers. The max values that array would every be per new lease or COLUMN B would be 35, most cases it never reaches that so i thought a dynamic array could just store whats there.
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
I'm not following what you're trying to do but maybe you can just load the array right off the sheet? It's easy enough to find the last used cell when it's time to reload the array.

Gary

Code:
Public Sub Test()

Dim iCount As Integer
Dim vArray As Variant

vArray = ActiveSheet.Range("B2:B35").Value

For iCount = LBound(vArray) To UBound(vArray)
    Debug.Print vArray(iCount, 1)
Next iCount

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,068
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top