joining and passing 2 range.areas.formula to an array index

Hi,

i am filtering a sheet data based on numbers (1 to 3) in a column. once i have the filtered range, i am passing this filtered range to an index of a variant array.

PHP Code: 

        Dim i As Long, Cnt As Long
        Cnt = Application.Evaluate("SumProduct(" & 1 & "/" & "CountIf(" & Rng.Address(external:=True) & "," & Rng.Address(external:=True) & "))")
        
        Dim Arr() As Variant

        For i = 1 To Cnt
            .AutoFilterMode = False
            .Cells.AutoFilter
            .Cells.AutoFilter field:=Col, Criteria1:=i

            Set Rng = .AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible)
            
            Dim Rng1 As Variant, Rng2 As Variant, URng As Variant
            ReDim Preserve Arr(1 To i)
            If Rng.Areas.Count > 1 Then
                Rng1 = Rng.Areas(1).Formula
                Rng2 = Rng.Areas(2).Formula
                Set URng = Union(Rng1, Rng2)
                
                Arr(i) = URng.Formula
                Arr(i) = Evaluate("if(" & uRng.Areas(2).Address(external:=True) & "<>0," & uRng.Areas(2).Address(external:=True) & ","""")")
            Else
                Arr(i) = Rng.Areas(1).Formula
                Arr(i) = Evaluate("if(" & Rng.Areas(1).Address(external:=True) & "<>0," & Rng.Areas(1).Address(external:=True) & ","""")")
            End If

......... 


Arr is a 1-D array containing 2-D Arrays in each of its indexes.

the Rng.Areas has the 1st column as headers which i want to insert in every Arr(i) indexes i.e. i want to insert the Headers that were found in Rng.Areas(1).formula as the 1st item in all the indexes (i) of Arr.

Arr looks like this in the Watch window, which shows first index contains headers:

PHP Code: 

Arr                                      Variant ( 1 to 3)
  L Arr(1)                              Variant (1 to 222, 1 to 69)
    L Arr(1)(1)                        Variant (1 to 69)
      L Arr(1)(1,1)      "POS1"             Variant/String       
      L Arr(1)(1,2)      "TAP1"             Variant/String 
      L Arr(1)(1,3)      "FRA1"             Variant/String 
      L Arr(1)(1,4)      "GRE1"             Variant/String 
....
    L Arr(1)(2)                        Variant (1 to 69)
      L Arr(1)(2,1)      1.9             Variant/String       
      L Arr(1)(2,2)      3.5             Variant/String 
      L Arr(1)(2,3)      2.1             Variant/String 
      L Arr(1)(2,4)      4.6             Variant/String 
....

  L Arr(2)                              Variant (1 to 222, 1 to 69)      ' i want to copy the above headers in this index too, as the 1st elements, somehow 
    L Arr(2)(1)                        Variant (1 to 69)                   ' expanding this dimension.
      L Arr(2)(1,1)      1.4             Variant/String       
      L Arr(2)(1,2)      2.1             Variant/String 
      L Arr(2)(1,3)      3.4             Variant/String 
      L Arr(2)(1,4)      5.5             Variant/String 
....
    L Arr(2)(2)                        Variant (1 to 69)
      L Arr(2)(2,1)      1.9             Variant/String       
      L Arr(2)(2,2)      3.5             Variant/String 
      L Arr(2)(2,3)      2.1             Variant/String 
      L Arr(2)(2,4)      4.6             Variant/String 
.... 


please let me know if this is possible.

sure JosephP,

let me attach a sample file for you to see.
give me a few minutes to format it and attach the code.

Hi JosephP,

i have attached the sample file in which the code lies.

the idea is to parse the data sheet values and get their means in the desired Result sheet format.

the list of 'Actions' are given in the Action sheet. just to give you a briefup:

1] Each Bucket has maximum of 9 Actions (<= 9, each having 6 Measures (FRA is not to be considered).
2] The Measure given in 1st row are numbered as NAD1, NAD2, NAD3 etc till NAD9. it could be less.
3] e.g. NAD1 here would represent Action 1's measure on NAD
NAD5 here would represent Action 5's measure on NAD
BEL6 here would represent Action 6's measure on BEL
4] so each Action number needs to be matched with the Measure number.
5] since the Measures are given only on the top row for 1st Bucket, there is a need to continue their numbering for subsequent Buckets. if you filter on the Bucket column, you will see that the 2nd and 3rd Buckets viz 1 & 2, have 8 Actions each.
so the numbering for Bucket 2 would be, e.g.
NAD10, NAD11, ...NAD17. Similarly for the other 5 measures..

and for Bucket 3 would be, e.g.
NAD18, NAD19...NAD25. Similarly for the other 5 measures..

So, basically the Means for these 6 measure viz., NAD, TEL, BEL, ADD, ALT, REL need to be taken for each Action and those Actions and their means across these measures shown in the Results sheet. The Buckets currently shown in the sheet are 3, each with :
Bucket 1 having 9 Actions.
Bucket 2 having 8 Actions.
Bucket 3 having 8 Actions.

But Buckets can range upto 11 Buckets i.e. 99 Actions. (see Actions sheet)

Please let me know if you can help me tweak the code to get the desired results.
Thanking you in advance.

Thanks JosephP,

and i am done for the day :P shall be online from home after a 2 hr journey.

hi,

this is really an interesting approach especially the way you found rows and columns and then applied to index function to get an array. You used a Filter to remove your plugged in "|" & get the remaining rows. Remarkable!

two things remain:
1] right now i am trying in vain to plugin the Dictionary code to calculate Means for each of the 6 measures using Index function. Do i need to have a UDT defined like this:

Type ArrMeasures
ActionName as String
NAD as Double
ADD as Double
TEL as Double
REL as Double
BEL as Double
ALT as Double
End Type

and then, define an array of that type like this?

ArrActionMeans() as ArrMeasures

2] i have defined a UDT function in another module which collects the Action names in a UDT. but problem is i cannot pass this function to a calling procedure. How can i collect these Action names into the above 1] ArrActionMeans().ActionName if i define such a UDT?