PDA

View Full Version : Adding a chart



3mustgetbeer
01-30-2011, 11:38 AM
Hi Guys,

I am pretty new to this and learning the basics - currently I am looking into adding a chart.

I found a pretty decent tutorial here:

peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#resizechart

I have added this code to the VB editor:


Sub EmbeddedChartFromScratch()
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long

' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub

' define chart data
Set rngChtData = Selection

' define chart's X values
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With

' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart

' make an XY chart
.ChartType = xlXYScatterLines

' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop

' add series from selected range, column by column
For iColumn = 2 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next

End With

End Sub

With the idea of seeing the results and working though so I understand what each part does and modifying it to suit my needs. However I get an error on this line:


Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)

The error is "Run-time error '1004'. Application-defined or object-defined error"

Any ideas guys???

Thanks alot

3MGB :beerchug:

Bob Phillips
01-30-2011, 01:09 PM
Works fine in my limited test. Can you post the workbook?

3mustgetbeer
01-30-2011, 02:16 PM
Hi mate

Looks like I fixed it - I wasn't selecting cells to start with - homer simpson moment I'm afraid!!

Thanks anyway thourgh :)