excel - How can I write a loop in vba for this code -
59.30 15 16 17 1 1,162,912,036.90 1,248,737,016.99 1,306,573,912.08 2 245,665,383.94 261,416,880.69 276,613,283.05 3 393,313,441.29 379,169,039.15 418,680,492.19 4 13,920,572.74 14,464,854.92 15,120,474.58 5 54,501,581.55 56,319,351.21 58,832,588.24 6 15,165,376.28 11,694,942.56 10,809,661.03 7 194,397,643.30 170,427,013.85 182,567,862.46 8 15,165,376.28 11,694,942.56 10,809,661.03 9 2,079,876,036.00 2,142,229,099.38 2,269,198,273.62 3% 6% there 7 tables above data in 1 excel tab in different area.i want create stacked column chart each table. wrote code create. want know possiable use loop solve problem? code attached.
sub formatchartnix() 'purpose: create chart (chart dimensions not required)
dim rng range dim cht object dim ser series dim tmpchr chartobject 'chart1 'your data range chart set rng = activesheet.range("b8:e17") 'create chart set cht = activesheet.shapes.addchart 'give chart data cht.chart.setsourcedata source:=rng, plotby:=xlrows 'determine chart type cht.chart.charttype = xlcolumnstacked activesheet .chartobjects(1).top = .range("c24").top .chartobjects(1).left = .range("c24").left end activesheet.chartobjects(1).activate activechart.axes(xlvalue).select selection.delete activechart.hastitle = true activechart.charttitle.text = activesheet.range("c1") 'chart2 set rng = activesheet.range("g8:j17") set cht = activesheet.shapes.addchart cht.chart.setsourcedata source:=rng, plotby:=xlrows cht.chart.charttype = xlcolumnstacked activesheet .chartobjects(2).top = .range("h24").top .chartobjects(2).left = .range("h24").left end activesheet.chartobjects(2).activate activechart.axes(xlvalue).select selection.delete activechart.hastitle = true activechart.charttitle.text = activesheet.range("h1") 'chart3 set rng = activesheet.range("l8:o17") set cht = activesheet.shapes.addchart cht.chart.setsourcedata source:=rng, plotby:=xlrows cht.chart.charttype = xlcolumnstacked activesheet .chartobjects(3).top = .range("m24").top .chartobjects(3).left = .range("m24").left end activesheet.chartobjects(3).activate activechart.axes(xlvalue).select selection.delete activechart.hastitle = true activechart.charttitle.text = activesheet.range("h1") 'chart4 set rng = activesheet.range("b82:e91") set cht = activesheet.shapes.addchart cht.chart.setsourcedata source:=rng, plotby:=xlrows cht.chart.charttype = xlcolumnstacked activesheet .chartobjects(4).top = .range("c51").top .chartobjects(4).left = .range("c51").left end activesheet.chartobjects(4).activate activechart.axes(xlvalue).select selection.delete activechart.hastitle = true activechart.charttitle.text = activesheet.range("c75") 'chart5 set rng = activesheet.range("g82:j91") set cht = activesheet.shapes.addchart cht.chart.setsourcedata source:=rng, plotby:=xlrows cht.chart.charttype = xlcolumnstacked activesheet .chartobjects(5).top = .range("h51").top .chartobjects(5).left = .range("h51").left end activesheet.chartobjects(5).activate activechart.axes(xlvalue).select selection.delete activechart.hastitle = true activechart.charttitle.text = activesheet.range("h75") 'chart6 set rng = activesheet.range("l82:o91") set cht = activesheet.shapes.addchart cht.chart.setsourcedata source:=rng, plotby:=xlrows cht.chart.charttype = xlcolumnstacked activesheet .chartobjects(6).top = .range("m51").top .chartobjects(6).left = .range("m51").left end activesheet.chartobjects(6).activate activechart.axes(xlvalue).select selection.delete activechart.hastitle = true activechart.charttitle.text = activesheet.range("m75") 'chart7 set rng = activesheet.range("q82:t91") set cht = activesheet.shapes.addchart cht.chart.setsourcedata source:=rng, plotby:=xlrows cht.chart.charttype = xlcolumnstacked activesheet .chartobjects(7).top = .range("r51").top .chartobjects(7).left = .range("r51").left end activesheet.chartobjects(7).activate activechart.axes(xlvalue).select selection.delete activechart.hastitle = true activechart.charttitle.text = activesheet.range("r75") end sub
using named ranges , arrays can loop through it. first, create named ranges ranges each chart.
i added small table in spreadsheet , named each 1 text in first cell of range (i.e., chart1,... chart7). other ranges each go in next cell, range named "chart 1" 4 cells.
(i used same ranges , cells did in code above)
chart1 b8:e17 c24 c1
chart2 g8:j17 h24 h1
chart3 l8:o17 m24 h1
chart4 b82:e91 c51 c75
chart5 g82:j91 h51 h75
chart6 l82:o91 m51 r75
chart7 q82:t91 r51 r75
sub formatchartnix_modified() dim rng range dim cht object dim ser series dim tmpchr chartobject dim myarray(1 7, 0 3) string dim integer = lbound(myarray) ubound(myarray) 'set values - possibly named ranges dim varray() variant dim strnamedrange string strnamedrange = "chart" & set rng = worksheets("sheet1").range(strnamedrange) varray = rng dim j integer j = lbound(myarray, 2) ubound(myarray, 2) myarray(i, j) = varray(1, j + 1) debug.print myarray(i, j) next j next = lbound(myarray) ubound(myarray) activesheet set rng = .range(myarray(i, 1)) '1 represents data range set cht = .shapes.addchart cht.chart.setsourcedata source:=rng, plotby:=xlrows cht.chart.charttype = xlcolumnstacked .chartobjects(i).top = .range(myarray(i, 2)).top '0 represents chart name .chartobjects(i).left = .range(myarray(i, 2)).left '2 represents cell identifying chart location .chartobjects(i).activate activechart .axes(xlvalue).select .axes(xlvalue).delete .hastitle = true .charttitle.text = activesheet.range(myarray(i, 3)).text '3 represents cell title text located end end next end sub do that, run sub , create 7 charts described in table - using loop.
Comments
Post a Comment