VBA Dynamic Array

[Pjam]

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.

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

 

[Atroxell]

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]

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

 

[Atroxell]

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...

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]

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]

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

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