arrays - Trouble sorting and aggregating cell data in excel using VBA -

i have updated this

update highlights

• changed part of code remove unnecessary commas in resultant sheet8.l5 field.
• also implemented suggestion suggested feelththis.
• now returns "1,9" instead of desired "1, 9, 29, 37, 50, 61"

original (slightly changed post)

i trying cell data 3 sheets, 5 cells per sheet total of fifteen cells. remove 0 values. numerically order remaining. insert single cell on sheet comma delimited. cell data should contain positive, whole numbers.

i have provided sample of data looks , code below. if there better way of approaching way attempting open other solutions.

the code below return error in aggregateseptember() line returns error has comment explaining it. thank feelththis.

after execution sheet 8 l5 should = "1, 9, 29, 37, 50, 61"

i totally stumped , haven't written vb before, appreciate this.

thanks in advance time , consideration, tim

the data below before vba runs. after code runs sheet8.l5.value = "1, 9, 29, 37, 50, 61" stated above.)

data

---

sheet 5 m5 n5 o5 p5 q5 r5 37 0  0  0  0  0  sheet 6 m5 n5 o5 p5 q5 r5 1  9  0  0  0  0  sheet 7 m5 n5 o5 p5 q5 r5 29 50 61 0  0  0  sheet 8 l5 0 

---

data

---

sub aggregateseptember()      dim integer     dim j integer     dim septemberterm1aggregate string     dim septemberterm1(0 14) integer     dim septemberterm2() integer     dim septembercols     septembercols = array("m5", "n5", "o5", "p5", "q5")      = 0 14         if < 5             if sheet5.range(septembercols(i)) <> 0                 septemberterm1(i) = sheet5.range(septembercols(i))             end if         elseif < 10             if sheet6.range(septembercols(i - 5)) <> 0                 septemberterm1(i - 5) = sheet6.range(septembercols(i - 5))             end if         elseif < 15             if sheet7.range(septembercols(i - 10)) <> 0                 septemberterm1(i - 10) = sheet7.range(septembercols(i - 10))             end if         end if     next      ' next line no longer returns error     septemberterm2 = bubblesrt(septemberterm1, true)      j = 0 14         if septemberterm2(j) > 0 septemberterm1aggregate = septemberterm1aggregate & septemberterm2(j)         if j > 0 , j < 14 , septemberterm2(j) > 0 septemberterm1aggregate = septemberterm1aggregate & ", "     next j      sheet8.range("l5").value = septemberterm1aggregate  end sub  public function bubblesrt(arrayin, ascending boolean)      dim srttemp variant     dim long     dim j long       if ascending = true         = lbound(arrayin) ubound(arrayin)              j = + 1 ubound(arrayin)                  if arrayin(i) > arrayin(j)                      srttemp = arrayin(j)                      arrayin(j) = arrayin(i)                      arrayin(i) = srttemp                  end if              next j          next     else         = lbound(arrayin) ubound(arrayin)             j = + 1 ubound(arrayin)                 if arrayin(i) < arrayin(j)                     srttemp = arrayin(j)                     arrayin(j) = arrayin(i)                     arrayin(i) = srttemp                 end if             next j         next     end if      bubblesrt = arrayin  end function  

---

well, seems faster me, here's solution anway. change "sheet1", ..., "sheet4" whatever need.

sub aggregateseptember()      dim                   integer  ' counter sheets     dim j                   integer  ' counter columns     dim k                   integer  ' counter data      dim vmysheets           variant  ' sheets     dim vseptembercols      variant  ' columns     dim icurrent            integer  ' current data     dim iseptemberterm()    integer  ' data array     dim saggregate          string   ' aggregate string      vmysheets = array("sheet1", "sheet2", "sheet3")     vseptembercols = array("m5", "n5", "o5", "p5", "q5", "r5")     redim iseptemberterm(0 (ubound(vmysheets) + 1) * (ubound(vseptembercols) + 1) - 1)      k = 0     = lbound(vmysheets) ubound(vmysheets)         j = lbound(vseptembercols) ubound(vseptembercols)             icurrent = thisworkbook.sheets(vmysheets(i)).range(vseptembercols(j)).value             if icurrent <> 0                 iseptemberterm(k) = icurrent                 k = k + 1             end if         next j     next      redim preserve iseptemberterm(0 k - 1) ' eliminate unused elements     iseptemberterm = bubblesrt(iseptemberterm, true)      = lbound(iseptemberterm) ubound(iseptemberterm)         saggregate = saggregate & iseptemberterm(i) & ", "     next      saggregate = left(saggregate, len(saggregate) - len(", "))     thisworkbook.sheets("sheet4").range("l5").value = saggregate  end sub 

a few notes:

• don't afraid throw in new counters, if needed :)
• you forgot put "r5" in septembercols
• you can reuse same counter in other loops (you use i in second for)
• note able make iseptemberterm = bubblesrt(iseptemberterm, true) because of how declared (without fixed bounds, can dinamically change it)