davidimurray
Technical User
Hi
I'm currently writing some code in VBA to automatically create charts in excel but I'm running into some difficulties.
The script I'm creating is generic and is used to graph two columns. The number of rows can vary on each run but both columns will always have the same number. So far I have written the following :-
Public Sub makechart(datasheet As String, xaxis As String, yaxis As String, name As String)
Dim achart As Chart
Dim lastr As Integer
lastr = lastrow(datasheet)
Set achart = Charts.Add
With achart
.ChartType = xlXYScatter
'ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='All Data'!R2C7:R74C7"
ActiveChart.SeriesCollection(1).Values = "='All Data'!R2C8:R74C8"
End With
End Sub
The function lastrow simply returns the last row number. What I want to do is control the ranges for the x and Y values but I can't seem to get it to work. I'm looking for something like -
ActiveChart.SeriesCollection(1).XValues = "='datasheet'!xaxis &"2"::xaxis &lastr"
How do I actually script this?
Cheers
Dave
I'm currently writing some code in VBA to automatically create charts in excel but I'm running into some difficulties.
The script I'm creating is generic and is used to graph two columns. The number of rows can vary on each run but both columns will always have the same number. So far I have written the following :-
Public Sub makechart(datasheet As String, xaxis As String, yaxis As String, name As String)
Dim achart As Chart
Dim lastr As Integer
lastr = lastrow(datasheet)
Set achart = Charts.Add
With achart
.ChartType = xlXYScatter
'ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='All Data'!R2C7:R74C7"
ActiveChart.SeriesCollection(1).Values = "='All Data'!R2C8:R74C8"
End With
End Sub
The function lastrow simply returns the last row number. What I want to do is control the ranges for the x and Y values but I can't seem to get it to work. I'm looking for something like -
ActiveChart.SeriesCollection(1).XValues = "='datasheet'!xaxis &"2"::xaxis &lastr"
How do I actually script this?
Cheers
Dave