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:
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: