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

Hey This Kotaiah , working L&T Infotech.
I did one application Leave Tracker. I want to make a chart that to dyanamically canges.
In sheet(2) i have data coloumns are
NAME DATEFROM DATETO REASON

how to write code for make a chart(any)

ASAP

Thanks&Regards
Kotaiah.K

kotaiah - do not hijack other peoples posts. If you have a question, post it in a new post

ok. Pls give solution for my request.

you could try
what is an example of xaxis -

Hi
Got it sorted now after some help on another forum - completed code is :-

Public Sub makechart(datasheet As String, xaxis As String, yaxis As String, name As String)

    Dim achart As Chart
    Dim lastr As String

   lastr = (lastrow(datasheet))

   If Not IsNumeric(xaxis) Then xaxis = Range(xaxis & "1").Column
   If Not IsNumeric(yaxis) Then yaxis = Range(yaxis & "1").Column



    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    'ActiveChart.SetSourceData Source:=Sheets("All Data").Range("A1:AV74"), _
        PlotBy:=xlColumns
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "='" & datasheet & "'!R2C" & xaxis & ":R" & lastr & "C" & xaxis
    ActiveChart.SeriesCollection(1).Values = "='" & datasheet & "'!R2C" & yaxis & ":R" & lastr & "C" & yaxis
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "WR V WR top"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "WR"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Vr"
    End With
    With ActiveChart.Axes(xlCategory)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    ActiveChart.HasLegend = False
    ActiveChart.PlotArea.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    Selection.Interior.ColorIndex = xlNone
End Sub

Cheers

Dave