Creating dynamic arrays in Excel VBA

Hi I have a bit of a problem again.....

I'll try to explain it as best as I can.

I need to dynamically determine how many different arrays to have.

Basically it works like this I have a sheet that has a certain number of "groups" with a particular value for each group:

Group Minimum Production
1 20000
2 30000
3 0
4 50000
5 18000
6 30000
7 10000
8 15000
9 15000
10 15000
11 20000
12 10000
13 10000
14 3500
15 10000
16 24000



I then have another sheet with a list of items, each item can belong to a group or not.

I need to somehow get an array that lists the groups like so:

Group (Group #,value for that group, row of item in group, row of other item in group, row of other item ingroup, etc)

The problem is I want to be able to read in how many groups there are and I don't have a way of knowing how many items are in the particular group.


I already have an array G (1 to # of items) that has the group number for each item row number. and can read in the value for each group easily enough.

I am currently at a lost.


Any help would be greatly appreciated

Thanks,
Nick

Hi,
I've rethought the way i was approaching it and now have the following:

The Array G() is the group number (if there is one) for the product itype.

groups =the number of different groups I have on the sheet

MinG() has been initalized:
ReDim Preserve MinG(1 To groups, 0)


I am looking for it to store MinG(Groups,How Many Items Are in each Group), and then have the value for MinG(2,0) = the minimum for group 2 and likewise MinG(2,1) = the row number for the 1st item in group 2.


Here is the code:

For q = 1 To groups

MinG(q, 0) = Worksheets("Group-Min").Range("MinData").Offset(q, 2).Value

For itype = 1 To skus
If G(itype) = q Then
ReDim Preserve MinG(q, UBound(MinG) + 1)
MinG(q, UBound(MinG)) = itype
Else
End If

Next itype

Next q

It is getting my personal favourite runtime error '9' on the ReDim Preserve Line.

This is the first time I have tried using the preserve function, does anyone know what I am doing wrong.

Thanks Again,
Nick

I'm fairly new to VBA as well, but I think if you leave off the ",o" on your redim statement you'll be ok.
So your code changes to.

ReDim Preserve MinG(groups)

I need to somehow get an array that lists the groups like so:

Group (Group #,value for that group, row of item in group, row of other item in group, row of other item ingroup, etc)


I would use User-defined Type (or UDT) for this.


Type myGroup
strName As String
intRow() As Integer
sngValue As Single
End Type

Dim myGroups() As myGroup
Sub Test()

Dim i As Integer, j As Integer

'Build the arrays
For i = 1 To 10
ReDim Preserve myGroups(1 To i)
myGroups(i).strName = "Group #" & i
ReDim Preserve myGroups(i).intRow(1 To 3)
For j = 1 To 3
myGroups(i).intRow(j) = j * 10
Next j
myGroups(i).sngValue = 1000 * i
Next i

'Now print their contents
For i = LBound(myGroups) To UBound(myGroups)
Debug.Print myGroups(i).strName, myGroups(i).sngValue
For j = LBound(myGroups(i).intRow) To UBound(myGroups(i).intRow)
Debug.Print myGroups(i).intRow(j)
Next j
Next i

End Sub

Thanks again Mill,
You are great at responding.

I have a problem however.

I don't know beforehand how many items are going to be in the group (the j=1 to 3 part). It could be 0 or up to like 20.

for the array G(item) will show the group number if its in a group, I have this read in already.

So I need to make a statement like:


For i = 1 To groups
ReDim Preserve myGroups(1 To group)
ReDim Preserve myGroups(i).intRow(0)
myGroups(i).strName = "Group #" & i

For j = 1 To items
If G(j) = i Then
ReDim Preserve myGroups(i).intRow(UBound(MyGroups(i).introw(+1)))
myGroups(i).intRow(Ubound(myGroups(i).intRow())) = j
End If
Next j
myGroups(i).sngValue = 1000 * i
Next i


But I can't get the Upper Bound Command to work.
Besides That the code is great though.

If you can think of a way around that, I would be very grateful.

Thanks,
Nick

Added VB tags - Mill

Try changing this line:


ReDim Preserve myGroups(i).intRow(UBound(MyGroups(i).introw(+1)))


to


ReDim Preserve myGroups(i).intRow(UBound(MyGroups(i).introw()) + 1)


The way you had it, you were trying to reference the (+1th) item of introw, and then redimension that item.

I have to say thank you very much, after a couple of changes I have it running now, this really really helps.

Hopefully I can start helping other novices soon.

Nick