Charting Problem

ramankumar

New Member
Joined
Sep 8, 2002
Messages
33
I have to draw a chart, whose series are always not constant. In other words for some cases the number of series are 3 and in some other cases the number of series are four. Is there a way to draw this type of graph using VBA. Any help is appreciated.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi ramankumar,

I would suggest you manually create the chart so that you can set the size and formatting exactly as you want it, but then let your VBA code add or subtract series depending on the need at the time. Here is a bit of code that might be instructed. I apologize in advance that there may be a bunch of extra stuff here to disctract you, but I don't want to leave anything out that might be helpful either. This code adds or subtracts series from a chart depending on how many columns of data exist on the PD worksheet. These columns are inserted or deleted by code earlier in the macro (not shown). nColsNew is the number of columns of data now, and nColsOld is the number of columns (series) of data the chart is currently plotting.

Application.StatusBar = "Modifying SA chart"

Dim LastPDrow As Integer
Dim TimeCol As Integer
Dim TimeRange As Range
Dim TempRange As Range
Dim NewSeries As Series

TimeCol = PD.Range("TimeColumn").Column
LastPDrow = PD.Range("TimeColumn").Offset(2).End(xlDown).Row
Set TimeRange = PD.Range(PD.Cells(4, TimeCol), PD.Cells(LastPDrow, TimeCol))
Col = PD.Range(ColName(1)).Column
Set TempRange = PD.Range(PD.Cells(4, Col), PD.Cells(LastPDrow, Col))

With Charts("SA Plot")

If nColsNew > nColsOld Then 'Increase in columns

For i = nColsOld + 1 To nColsNew
Set NewSeries = .SeriesCollection.NewSeries
NewSeries.MarkerStyle = xlMarkerStyleNone
NewSeries.Name = "Panel Type" & Str(i) & " Temp."
NewSeries.AxisGroup = 2
NewSeries.Values = TempRange.Offset(0, i - 1)
Next i

Else

' Assume Temp. series are last series added to chart
j = .SeriesCollection.Count
For i = j To j - nColsOld + nColsNew + 1 Step -1
.SeriesCollection(i).Delete
Next i

End If

End With


If your chart is embedded on a worksheet the

With Charts("SA Plot")

would become something like

With ChartObjects("SA Plot").Chart

assuming that you had given it the name "SA Plot" (in the formula bar Name box above cell A1).

I hope this helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top