stacked chart vba, variable number of row

I tried to record a makro for the proccess and then modify it with "last row". Which i saw you use in the following


Set Newcht = .Shapes.AddChart(xlColumnStacked) 'you can add chart position and size here too.
Newcht.Chart.SetSourceData Source:=.Range("A5:C" & LastRow)

for the linechart i got the following gode:
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Ark2'!$D$5"
ActiveChart.SeriesCollection(1).Values = "='Ark2'!$D$6:$D$63"
ActiveChart.SeriesCollection(1).XValues = "='Ark2'!$E$6:$E$63"

i got

ActiveChart.SeriesCollection(1).Name = "='Ark2'!$D$5"
ActiveChart.SeriesCollection(1).Values = "='Ark2'!$D$6: LastRow"
ActiveChart.SeriesCollection(1).XValues = "='Ark2'!$E$6: Last row"
didnt work

i tried adding xlLine to your line.
i got :
Set Newcht = .Shapes.AddChart(xlLine) 'you can add chart position and size here too.
Newcht.Chart.SetSourceData Source:=.Range("D6:E" & LastRow)

Didnt work either .. :p
 
subazz said:
does it matter if its a XY or a line ?.. im just trying to show time as a function of percentage.. so that i can eventually tell that 90% has been handled within e.g 3 hours
Click to expand...
In that case it probably doesn't matter - but I needed to know so as not to give you duff advice.
Using XY scatter in your case would allow more flexibility; the category axis starts at 2, not 0, so when it comes to adding tick-marks it adds them 2,8,14 and so on, unusual but more user friendly to use 0,10,20 etc. which can be done using XY Scatter.

For a line chart (NOT XY Scatter) change the chart-creating code at the end to:
Code: 
  Set NewCht = .Shapes.AddChart(xlColumnStacked)  'you can add chart position and size here too.
  NewCht.Chart.SetSourceData Source:=.Range("A5:C" & LastRow)

  Set NewCht2 = .ChartObjects.Add(Left:=NewCht.Left + NewCht.Width, Width:=NewCht.Width, Top:=NewCht.Top, Height:=NewCht.Height)  'same size as first chart but immediately to its right.
  With NewCht2.Chart
    .ChartType = xlLineMarkers 'can be xlLine if you prefer
    With .SeriesCollection.NewSeries
      .XValues = NewSht.Range("E6:E" & LastRow)
      .Values = NewSht.Range("D6:D" & LastRow)
    End With  '.SeriesCollection.NewSeries
  End With  'NewCht2.Chart

The attached file contains code for an XY Scatter plot.
 

Attachments

  • excelguru4911_pd06.xlsm
    74.1 KB · Views: 12
Pardon my late reply! i havent been in office since weednesday.
They code works perfectly!. Thanks alot!
I wish i rocked at this like you do!.
do you have any recomondation on how to improve my vba skills ?
 
You must log in or register to reply here.
Back
Top