Solved: Multidimensional Arrays

[next]

I can't seem to figure out how to create a dynamic 2d array in this fashion:
array(states)(0) = NY
array(states)(1) = NJ
array(colors)(0) = red
array(colors)(1) = blue

Is this possible?

[Bob Phillips]

Wouldn't your arrays be states and colors, something like



Dim states(0 To some_number) As String
Dim colors(0 To another_number) as String

states(0) = "NY"
states(1) ="NJ"
colors(0) = "red"
colors(1) = "blue"

[next]

Nah, I need a 2D array, not 2 arrays. Here's what I'm trying to do:
For instance, I have a sheet with 3 rows of info, these rows represent jobs performed by individuals, so it looks something like this: indiv# 1 - job 523, indiv# 2 - job 655, indiv# 2 - job 122.
So one of them did multiple jobs; I need to store all this in 2D array to later loop through first dimension and create emails to all people stored in it. These emails will contain second array element, which is the jobs performed, in email body.
So for the example above, there should be 2 emails created. First one for indiv# 1, referencing job 523 and what I need with it. Second one should be for indiv# 2, referencing jobs 655 and 122.

Hopefully this makes sense : pray2: .

[Kenneth Hobs]

Working with two one dimensional arrays is usually easier than one two dimensional array. If you are working with ranges, then arrays may not be needed at all.

So, to do what you want:
Sub test()
Dim a(), i As Integer, j As Integer
a() = Range("A2:B" & ActiveSheet.UsedRange.Rows.Count)
For i = 1 To UBound(a, 1)
For j = 1 To UBound(a, 2)
Debug.Print a(i, j)
Next j
Next i
End Sub

[dannohayes]

See how this works:

Dim ary1(0 To some_number, 2) As String

ary(1, 1) = "indiv# 1"
ary(1, 2) = "job 523"
ary(2, 1) = "indiv# 2"
ary(2, 2) = "job 655"
ary(3, 1) = "indiv# 2"
ary(3, 2) = "job 122"

dimension 1 is the incidence, dimension 2 is name / job.

[Bob Phillips]

Why not create a job class and a jobs class and then you can loop through all jobs to create the email. Tidier IMO.

[next]

dannohayes, that would create multiple emails to the same individuals. I would prefer not to spam people :rotlaugh: .

xld, I would imagine you are correct, but I never got around to learning classes in VBA, so arrays is my natural choice.

I think I'm getting closer to what I need accomplished, faced with a problem though:
Sub notifications()
Dim LastCell As Integer
LastCell = GetLastCell()
Dim Crews() As Variant, WorkOrders() As Variant
Dim Index As Integer, i As Integer
Dim Cell As Range
Dim TestArray As Variant

For Each Cell In Range("I:I")
If IsEmpty(Cell) Then Exit For
ReDim Preserve Crews(0 To Index + 1)

TestArray = Filter(Crews, Cell)

'duplicate crew found
If UBound(TestArray) > -1 Then
i = i + 1
WorkOrders(UBound(WorkOrders, 1), i) = Cell.Offset(0, -7)

'no duplicates found
Else
ReDim Preserve WorkOrders(0 To Index + 1, 0 To i + 1)
Crews(Index) = Cell
WorkOrders(Index, i) = Cell.Offset(0, -7)
Index = Index + 1
i = 0
End If
Next Cell

'Debug.Print WorkOrders(1, 1)
End Sub
I get a "subscript out of range" error at ReDim Preserve WorkOrders(0 To Index + 1, 0 To i + 1) . Changing Index + 1 expression to a constant digit solves the issue, but I can't have that, It has to be dynamic. How should I go about resizing WorkOrders array?

Here's an example of an actual file that I'm working this on:

Req on File W.O.No. Cli Loc # Lev Description Sched Dt Resched Crew
*R*, 651972, AEP, 31, A1, FLOOR CLEANING SERVICE, 9/23/2010, NY-378
*R*, 662439, AEP, 37, A4, HIGH DUST FOR INVOICE PURPOSES ONLY!, 10/1/2010, NJ-107
*R*, 662465, AEP, 46, A4, HIGH DUST FOR INVOICE PURPOSES ONLY!, 10/1/2010, NJ-107
*R*, 662420, AEP, 51, A4, HIGH DUST FOR INVOICE PURPOSES ONLY!, 10/1/2010, NJ-107

[Bob Phillips]

Would you like me to knock you up an example? if yes, can you post what you have now?

[next]

That would be nice, thanks. My previous post is what I have so far. I found out that I can resize only last dimension in 2+ dim arrays, so I just need to correct some other error and it should work fine (hopefully).
Basically of the bottom example 2 arrays should be created. One for NY-378 with job number 651972 tied to him, and the second one for NJ-107 with 3 jobs tied to him: 662439, 662465 and 662420.
If you can build an example that would do that, I could go from there.
Thanks a bunch.

[Bob Phillips]

You can't have dynamic arrays dynamic in 2-dimensions, only the last dimension can be dynamic, so you may be stymied.

But, I am talking about another way. The workbook would help to see the data.

[next]

I uploaded an example of a wb. Thanks.

[next]

I went through a quick tutorial and omg, this is so much easier with classes.
Thanks for the suggestions everyone.
xld, I already coded the class, so no need for an example code, thanks.

[Bob Phillips]

This should show you what I mean

[Bob Phillips]

I went through a quick tutorial and omg, this is so much easier with classes.
Thanks for the suggestions everyone.
xld, I already coded the class, so no need for an example code, thanks.

I'll bet it is not as powerful as the one I created :)

[next]

I bet it's not lol. Can you save it in Office 2003 compatible format?

[Bob Phillips]

Here you are.

BTW, don't you have the compatibility viewer?

[next]

xld, nice example. i already completed my small, but awesome, project! Thanks a bunch!

[Bob Phillips]

xld, nice example. i already completed my small, but awesome, project! Thanks a bunch!

Okay, post it here for the archives.