excel - Need Help Making a Dynamic Chart -

good afternoon! college student co-oping chemical company on summer. have completed 1 basic course in excel vba programming, lack solid foundation of how language works.

recently, given task of building upon code 1 of predecessors wrote, intent generate dynamic graphs.

the objective this: write code display company's 17 different products on 2 year period on 4 different graphs (one per reactor). workbook has 6 different sheets of data, 1 of has button user press initiate necessary data calculations, cell population, , graph generation.

there no way know how many batches of each of our 17 products make on course of 2 years since dependent on customer demand. code needs dynamically account this.

i have reviewed class material 2 years ago , purchased john walkenbach's 2015 vba power programming book. teaching myself vba code, still not understand how of works. hope here may able explain why code not working , should fix it.

i started intent graph single product's 2 batches using overall time took make each batch (cycle time) , dates on batches finished y , x values, respectively. code looked this:

sub chart_excerpt()  sheets.add after:=sheets(sheets.count)     sheets(sheets.count).select     sheets(sheets.count).name = "g350 charts" activesheet.shapes.addchart.select     activechart.charttype = xlxyscatter     activechart.seriescollection(1).xvalues = "='biyearly  report'!$cy$802:$cy$803"     activechart.seriescollection(1).values = "='biyearly  report'!$cz$802:$cz$803"     end sub 

that code works, however, not dynamic. tried altering last 2 lines before end sub instead:

activechart.seriescollection(1).values = range (cells(802, 103), cells(803, 103)) activechart.seriescollection(1).values = range (cells(802, 104), cells(803, 104)) 

i tried replace specific, non-dynamic references better such abc , xyz:

activechart.seriescollection(1).values = range (cells(abc, 103), cells(xyz, 103)) activechart.seriescollection(1).values = range (cells(abc, 104), cells(xyz, 104)) 

where abc , xyz first , last rows of data selection, respectively. calculated using simple formula verified works correctly , based on section of code written former co-op student. data entry vertical, column numbers constant while number of rows change depending on number of batches make per product.

this didn't work, took variables out , retyped specific references 802 803. surprise, didn't work. i've learned since excel vba reads data arrays series collection, know must have set myself failure attempting force read range.

however, isn't working arrays either. below new code:

sub chart_excerpt() dim a() double, aa integer, s series, result variant, n integer  '''everything in above excerpt here [], commented inactive  call data_series  end sub 

''''''''''''''''''''''''''''''''

sub data_series dim xrange range, yrange range, result variant, s series  'aa calculated xyz example above , provides value 'aa , cc2311endrowbr defined 'public aa integer' , _      'public cc2231endrowbr integer' in code aa = cc2311endrowbr redim a(802 aa, 103 104)  call read_data_g350_cc2311(a, n, 0, 0)  set s = activesheet.chartobjects.chart.seriescollection  result = a(s)  activesheet.shapes.addchart.select     activechart.charttype = xlxyscatter     set xrange = range(result())     set yrange = range(result())         s.xvalues = xrange         s.values = yrange  end sub 

'''''''''''''''''''''''''''''

sub read_data_g350_cc2311(a() double, n integer, r integer, c integer) dim integer, j integer  n = aa 'aa defined 'public aa integer' in code  = 802 n     j = 103 104         a(i, j) = cells(i+r, j+c)     next j next  end sub 

unfortunately, no matter do, continue receive error messages along lines of "subscript out of range," "user defined error," "object missing," etc. there way dynamically code chart this? have suggestion should try next?

update:

i able complete code , working perfectly! thank byron pointing me in right direction!

below new, working code in case else comes similar problem , needs assistance:

sub graphing_for_g350(xval range, yval range, location integer)  dim height double, width double, columns integer, cht_obj chartobject, ser series  dim k integer, sindex integer, double, j integer, n integer  'determines chart size , location  height = 300  width = 300  columns = 1   j = 0  k = 0  n = 0  set cht_obj = activesheet.chartobjects.add((1 mod columns) * width, (1 \ columns) * height, width, height)   'determines series order based on product order in company's spreadsheet  sindex = 1 17    if sindex = 1 , cc2311endrowbr > 801          = cc2311endrowbr      elseif sindex = 2 , dr22endrowbr > 801          = dr22endrowbr      elseif sindex = 3 , dr22ncendrowbr > 801          = dr22ncendrowbr      elseif sindex = 4 , ncyendrowbr > 801          = ncyendrowbr      elseif sindex = 5 , re100lendrowbr > 801          = re100lendrowbr      elseif sindex = 6 , re100xlendrowbr > 801          = re100xlendrowbr      elseif sindex = 7 , re105endrowbr > 801          = re105endrowbr      elseif sindex = 8 , re110endrowbr > 801          = re110endrowbr      elseif sindex = 9 , re25endrowbr > 801          = re25endrowbr      elseif sindex = 10 , re80hpendrowbr > 801          = re80hpendrowbr      elseif sindex = 11 , re85endrowbr > 801          = re85endrowbr      elseif sindex = 12 , re85kendrowbr > 801          = re85kendrowbr      elseif sindex = 13 , re85lendrowbr > 801          = re85lendrowbr      elseif sindex = 14 , re85lkendrowbr > 801          = re85lkendrowbr      elseif sindex = 15 , re98endrowbr > 801          = re98endrowbr      elseif sindex = 16 , xr4318endrowbr > 801          = xr4318endrowbr      elseif sindex = 17 , xr4265endrowbr > 801          = xr4265endrowbr      else      end if   'this loop j determines xval columns of series.           if j = 0              j = 103           elseif j > 0               j = j + 3           end if  'this loop n determines yval columns of series.           if n = 0              n = 104           elseif n > 0               n = n + 3           end if   'this determines range respect product chosen in if-then statements above.      set xval = range(cells(802, j), cells(a, j))      set yval = range(cells(802, n), cells(a, n))  'now add series , name it.      set ser = cht_obj.chart.seriescollection.newseries          ser          ser.charttype = xlxyscatter   'this loop k determines name of series.           if k = 0                k = 102           elseif k > 0               k = k + 3           end if  'defines series name , values          ser.name = cells(802, k)          ser.values = yval          ser.xvalues = xval         end     'iterates next series index decide next series.  next sindex  end sub 

in opinion, think different way of doing this, do? create dinamic table in hidden sheet , pull slider it, there, pull dinamic graph dinamic table, , macro change selection in slider, make graph dinamic, other way this, if know columns going show (sumifs(...)), simple use series of formulas , point graph formulas, , macro change keyword formulas use keyword(product) information.