Simplifying this quite a bit ...
1. I have four sheets three of them are being aggregated into the fourth.
2. Each populated cell on the fourth sheet is an aggregate of fifteen cells from the other three sheets (5 cells per sheet).
3. However to complicate matters before they are comma delimited and written out to the cell they need to be sorted and all zero values eliminated.
For example
Sheet1.M5 = 37 Sheet1.N5 = 0 Sheet1.O5 = 0 Sheet1.P5 =0 Sheet1.Q5 = 0
Sheet2.M5 = 1 Sheet2.N5 = 9 Sheet2.O5 = 0 Sheet2.P5 =0 Sheet2.Q5 = 0
Sheet3.M5 = 29 Sheet3.N5 = 50 Sheet3.O5 = 61 Sheet3.P5 =0 Sheet3.Q5 = 0
Should aggregate to
Sheet4.L5 = "1, 9, 29, 37, 50, 61"
I started with
' The flattened array with all zero value elements removed and comma delimited
Dim SeptemberTerm1Aggregate As String
' Values to be sorted
Dim SeptemberTerm1(0 To 14) As Integer
' Populating the array from the other 3 sheets
SeptemberTerm1(0) = Sheet5.Range("M5").Value
SeptemberTerm1(1) = Sheet5.Range("N5").Value
SeptemberTerm1(2) = Sheet5.Range("O5").Value
SeptemberTerm1(3) = Sheet5.Range("P5").Value
SeptemberTerm1(4) = Sheet5.Range("Q5").Value
SeptemberTerm1(5) = Sheet6.Range("M5").Value
SeptemberTerm1(6) = Sheet6.Range("N5").Value
SeptemberTerm1(7) = Sheet6.Range("O5").Value
SeptemberTerm1(8) = Sheet6.Range("P5").Value
SeptemberTerm1(9) = Sheet6.Range("Q5").Value
SeptemberTerm1(10) = Sheet7.Range("M5").Value
SeptemberTerm1(11) = Sheet7.Range("N5").Value
SeptemberTerm1(12) = Sheet7.Range("O5").Value
SeptemberTerm1(13) = Sheet7.Range("P5").Value
SeptemberTerm1(14) = Sheet7.Range("Q5").Value
' Using the BubbleSrt function from here http://www.mrexcel.com/forum/excel-...al-basic-applications-sort-array-numbers.html
SeptemberTerm1 = BubbleSrt(SeptemberTerm1, True)
' Remove all zero value elements
' Flatten array
Sheet8.Range("L5").Value = SeptemberTerm1Aggregate
Only I'm not sure how to drop all zero value array elements and flatten the array. Or even if this is the best way to go about accomplishing my goal. The five cells from each of the first three sheets might have zero to five significant values. A significant value being a positive non-zero integer.
Any help would be appreciated.
Thank you,
Tim
1. I have four sheets three of them are being aggregated into the fourth.
2. Each populated cell on the fourth sheet is an aggregate of fifteen cells from the other three sheets (5 cells per sheet).
3. However to complicate matters before they are comma delimited and written out to the cell they need to be sorted and all zero values eliminated.
For example
Sheet1.M5 = 37 Sheet1.N5 = 0 Sheet1.O5 = 0 Sheet1.P5 =0 Sheet1.Q5 = 0
Sheet2.M5 = 1 Sheet2.N5 = 9 Sheet2.O5 = 0 Sheet2.P5 =0 Sheet2.Q5 = 0
Sheet3.M5 = 29 Sheet3.N5 = 50 Sheet3.O5 = 61 Sheet3.P5 =0 Sheet3.Q5 = 0
Should aggregate to
Sheet4.L5 = "1, 9, 29, 37, 50, 61"
I started with
' The flattened array with all zero value elements removed and comma delimited
Dim SeptemberTerm1Aggregate As String
' Values to be sorted
Dim SeptemberTerm1(0 To 14) As Integer
' Populating the array from the other 3 sheets
SeptemberTerm1(0) = Sheet5.Range("M5").Value
SeptemberTerm1(1) = Sheet5.Range("N5").Value
SeptemberTerm1(2) = Sheet5.Range("O5").Value
SeptemberTerm1(3) = Sheet5.Range("P5").Value
SeptemberTerm1(4) = Sheet5.Range("Q5").Value
SeptemberTerm1(5) = Sheet6.Range("M5").Value
SeptemberTerm1(6) = Sheet6.Range("N5").Value
SeptemberTerm1(7) = Sheet6.Range("O5").Value
SeptemberTerm1(8) = Sheet6.Range("P5").Value
SeptemberTerm1(9) = Sheet6.Range("Q5").Value
SeptemberTerm1(10) = Sheet7.Range("M5").Value
SeptemberTerm1(11) = Sheet7.Range("N5").Value
SeptemberTerm1(12) = Sheet7.Range("O5").Value
SeptemberTerm1(13) = Sheet7.Range("P5").Value
SeptemberTerm1(14) = Sheet7.Range("Q5").Value
' Using the BubbleSrt function from here http://www.mrexcel.com/forum/excel-...al-basic-applications-sort-array-numbers.html
SeptemberTerm1 = BubbleSrt(SeptemberTerm1, True)
' Remove all zero value elements
' Flatten array
Sheet8.Range("L5").Value = SeptemberTerm1Aggregate
Only I'm not sure how to drop all zero value array elements and flatten the array. Or even if this is the best way to go about accomplishing my goal. The five cells from each of the first three sheets might have zero to five significant values. A significant value being a positive non-zero integer.
Any help would be appreciated.
Thank you,
Tim