Arrays

[Darren Bartrup]

Hi all,

I'm having trouble sorting an array out - maybe something to do with my brain melting in all this heat at the moment (no such thing as air conditioning here).
Anyway, here's my array:

• strEmail(1,1) = Email_Address1
  strEmail(1,2) = OT
  strEmail(1,3) = PSI
  
  strEmail(2,1) = Email_Address2
  strEmail(2,2) = LD
  strEmail(2,3) =
  
  strEmail(3,1) = Email_Address3
  strEmail(3,2) = All
  strEmail(3,3) =
  
  strEmail(4,1) = Email_Address4
  strEmail(4,2) = LD
  strEmail(4,3) =
  
  strEmail(5,1) = Email_Address5
  strEmail(5,2) = MH
  strEmail(5,3) =
  
  strEmail(6,1) = Email_Address6
  strEmail(6,2) = OP
  strEmail(6,3) =
  
  strEmail(7,1) = Email_Address7
  strEmail(7,2) = MH
  strEmail(7,3) =
  
  strEmail(8,1) = Email_Address8
  strEmail(8,2) = CH
  strEmail(8,3) =

The first element is the email address and the second & third elements represent worksheet names. What I'd like to be able to do is rearrange the array so that it looks like this:

• SomeOtherArray(1,1) = Email_Address1
  SomeOtherArray(1,2) = OT
  SomeOtherArray(1,3) = PSI
  
  SomeOtherArray(2,1) = Email_Address2; Email_Address4
  SomeOtherArray(2,2) = LD
  SomeOtherArray(2,3) =
  
  SomeOtherArray(3,1) = Email_Address3
  SomeOtherArray(3,2) = All
  SomeOtherArray(3,3) =
  
  SomeOtherArray(4,1) = Email_Address5; Email_Address7
  SomeOtherArray(4,2) = MH
  SomeOtherArray(4,3) =
  
  SomeOtherArray(5,1) = Email_Address6
  SomeOtherArray(5,2) = OP
  SomeOtherArray(5,3) =
  
  SomeOtherArray(6,1) = Email_Address8
  SomeOtherArray(6,2) = CH
  SomeOtherArray(6,3) =

This will then be used to create an email and attach a workbook that shows only the worksheets named in the second and third elements - the other worksheets will be very hidden.

Any help is greatly appreciated!

 

[hatman]

Something like this? (untested):

'assume that the source array is already populated at this time, named arr1

dim arr2() as string

redim arr2(1 to 3, 1 to 1)

for cnt = 1 to 3

arr2(cnt,1) = arr1(1,cnt)

next cnt

for cnt = 1 to ubound(arr1,1)

flag = true

for cnt2 = 1 to ubound(arr2,2)

if arr1(cnt,2) = arr2(2,cnt2) and arr1(cnt,3) = arr2(3,cnt2) then

arr2(1,cnt2) = arr2(1,cnt2) & "; " & arr1(cnt,1)

flag = false

exit for

end if

next cnt2

if flag then

redim preserve arr2(1 to 3, 1 to ubound(arr2,2) +1)

for cnt3 = 1 to 3

arr2(cnt3,ubound(arr2,2)) = arr1(cnt,cnt3)

next cnt3

end if

next cnt

 

[Darren Bartrup]

That's great! Thanks hatman!
I just had to make one minor adjustment as the first element was showing

• arr2(1,1) = Email_Address1; Email_Address1
  arr2(1,2) = OT
  arr2(1,3) = PSI

The final code was:

    ReDim arr2(1 To 3, 1 To 1)

    For cnt = 1 To 3
        arr2(cnt, 1) = arr1(1, cnt)
    Next cnt
    For cnt = 1 To UBound(arr1, 1)
        flag = True
        For cnt2 = 1 To UBound(arr2, 2)
        If arr1(cnt, 1) = arr2(1, cnt2) Then              '<<<< 
            flag = False                                  '<<<< Minor adjustment
        Else                                              '<<<<
            If arr1(cnt, 2) = arr2(2, cnt2) And arr1(cnt, 3) = arr2(3, cnt2) Then
                arr2(1, cnt2) = arr2(1, cnt2) & "; " & arr1(cnt, 1)
                flag = False
                Exit For
            End If
        End If                                             '<<<<
        Next cnt2
        If flag Then
            ReDim Preserve arr2(1 To 3, 1 To UBound(arr2, 2) + 1)
            For cnt3 = 1 To 3
                arr2(cnt3, UBound(arr2, 2)) = arr1(cnt, cnt3)
            Next cnt3
        End If
    Next cnt