Generating Charts with VBA

[davidimurray]

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

 

[PHV]

Something like this ?
ActiveChart.SeriesCollection(1).XValues = "='" & datasheet & "'!R2C" & xaxis & ":R" & lastr & "C" & xaxis

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidimurray]

Hi PHV

That's great, cured the main problem. Is it possible to convert a column heading to a number? I would like to define the respective columns using the letters - e.g.

Currently -
call makechart("All Data", "7", "8", "WR vs VR")
But I would prefer -
call makechart("All Data", "G", "H", "WR vs VR")

Thanks for the help.

Cheers

Dave

 

[PHV]

Like this ?
If Not IsNumeric(xaxis) Then xaxis = Range(xaxis & "1").Column

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidimurray]

Just the job - awesome. Thanks for the help PHV

Cheers

Dave