excel - Searching a list to make different series, then plotting their corresponding values -

let me start out saying new both site , vba (i took crash course in high school, basic coding jargon). have been looking several days find code work need, haven't found anything.

basically have bunch of sand samples have been sieved. every sample gets own sheet. here, have master summary sheet collects data need plot these other sheets , puts table. finds type of sample , date of test. there 6 different types of sample (so far).

also, need summary table plotted date on x-axis, , percent on y-axis. need each sample type have own series. have gotten have 6 series divided correctly (though sure code awfully inefficient) can't figure out how value column next sample type plotted. in other words, stuck @ value of "0" because sorting string right now.

i've put code , text version of excel sheet below. appreciate can give me!

'sheet           date       type    sieve #40 'truck 47533    4/15/2016   truck       55% 'truck 47272    4/4/2016    truck       55% '47272          4/4/2016    coa         48% 'basement 4-4   4/4/2016    basement    55% 'bin2 4-4       4/4/2016    bin2        55% 'bin1 4-4       4/4/2016    bin1        55% 'hopper 4-4     4/4/2016    hopper      57% 'basement 4-1   4/1/2016    basement    58% 'bin2 4-1       4/1/2016    bin2        54% 'bin1 4-1       4/1/2016    bin1        58% 'hopper 4-1     4/1/2016    hopper      56% 'truck 46892    4/1/2016    truck       56% '46892          4/1/2016    coa         47% 'basement 3-24  3/24/2016   basement    55% 'bin2 3-24      3/24/2016   bin2        57% 'bin1 3-24      3/24/2016   bin1        61% 'hopper 3-24    3/24/2016   hopper      50%      sub chartingsub()  dim lastrow long dim c range dim rng1 range dim truck range dim hopper range dim bin1 range dim bin2 range dim basement range dim coa range dim newsand range dim shname string dim dates range      if activesheet.chartobjects.count > 0         activesheet.chartobjects.delete     end if      activesheet         lastrow = .range("d" & .rows.count).end(xlup).row         set rng1 = .range("c2:c" & lastrow)         shname = .name     end  activesheet     set dates = .range("b2:b" & lastrow) end      each c in rng1         if c.value = "truck"             if not truck nothing                 set truck = union(truck, c)             else                 set truck = c             end if           elseif c.value = "hopper"             if not hopper nothing                 set hopper = union(hopper, c)             else                 set hopper = c             end if         elseif c.value = "bin1"             if not bin1 nothing                 set bin1 = union(bin1, c)             else                 set bin1 = c             end if         elseif c.value = "bin2"             if not bin2 nothing                 set bin2 = union(bin2, c)             else                 set bin2 = c             end if         elseif c.value = "basement"             if not basement nothing                 set basement = union(basement, c)             else                 set basement = c             end if         elseif c.value = "coa"             if not coa nothing                 set coa = union(coa, c)             else                 set coa = c             end if         elseif c.value = "newsand"             if not newsand nothing                 set newsand = union(newsand, c)             else                 set newsand = c             end if         end if     next  dim cht chart set cht = activeworkbook.charts.add set cht = cht.location(where:=xllocationasobject, name:=shname)     cht         .charttype = xlxyscatterlines         .hastitle = true         .charttitle.text = "sieve #40 trend"     end  dim t series set t = cht.seriescollection.newseries t     .values = truck     .xvalues = dates     .name = "truck" end  dim h series set h = cht.seriescollection.newseries h     .values = hopper     .xvalues = dates     .name = "hopper" end  dim b series set b = cht.seriescollection.newseries b     .values = basement     .xvalues = dates     .name = "basement reclaim" end  dim b1 series set b1 = cht.seriescollection.newseries b1     .values = bin1     .xvalues = dates     .name = "bin1" end  dim b2 series set b2 = cht.seriescollection.newseries b2     .values = bin2     .xvalues = dates     .name = "bin2" end  dim cert series set cert = cht.seriescollection.newseries cert     .values = coa     .xvalues = dates     .name = "coa" end  'dim ns series 'set ns = cht.seriescollection.newseries 'with ns     '.values = newsand     '.xvalues = dates     '.name = "new resin sand" 'end  end sub 

if need plot x date, y percent, , "z" type. need following:

1. in excel need create each series individually - if have 5 types need plot each 1 separately.

2. the easiest way sort type, loop on rows, , figure out boundaries type 1 maybe rows 2-11, type 2 12-15, etc.

3. then can plot each series

something - start , end found using above:

for = 1 lastrow     activechart              .seriescollection.newseries                  .xvalues = sheets(strname).range("e" & startx & ":e" & endx)                 .values = sheets(strname).range("e" & starty & ":e" & endy)                 .name = strname              end          end if      end  next