I am trying to write a macro to create a plot in Excel. However the range of
the plot is different and it cant be hardcoded. Can anyone help with this.
The code i am using now is below, but it is given me error 1004. Where the
"**" are is where the error occurs


Private Sub VRH1H4Chartss()
'Option Explicit

Dim LastCellVRH1H4x As Long
Dim LastCellVRH1H4y As Long

On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1H4").Delete
On Error GoTo 0

With Sheets("SerialTable")
LastCellVRH1H4x = .Cells(.Rows.Count, "F").End(xlUp).Row
LastCellVRH1H4y = .Cells(.Rows.Count, "K").End(xlUp).Row
End With

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1H4"
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues _
= Worksheets("SerialTable").Range("f2:F" & LastCellVRH1H4x) _
.Address(external:=True)
.SeriesCollection(1).Values _
= Worksheets("SerialTable").Range("k2:K" &
LastCellVRH1H4y).Value
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
'.PlotArea.Top = 18
'.PlotArea.Height = 162
'.Axes(xlValue).MaximumScale = 0.6
'.Deselect
End With
Application.ScreenUpdating = True
Sheets("Program").Activate
End Sub

why not create your chart and then change to use a defined name instead
on the sheet where you need the nameeditnamesdefinename as desiredin
the refers to box
=offset($f$2,0,0,counta($f:$f)-1,1)
now this will autochange. Then in the series
=yourworkbookname.xls!namedrangename

--
Don Guillett
SalesAid Software

"CLamar" wrote in message
...
I am trying to write a macro to create a plot in Excel. However the range
of
the plot is different and it cant be hardcoded. Can anyone help with
this.
The code i am using now is below, but it is given me error 1004. Where
the
"**" are is where the error occurs


Private Sub VRH1H4Chartss()
'Option Explicit

Dim LastCellVRH1H4x As Long
Dim LastCellVRH1H4y As Long

On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1H4").Delete
On Error GoTo 0

With Sheets("SerialTable")
LastCellVRH1H4x = .Cells(.Rows.Count, "F").End(xlUp).Row
LastCellVRH1H4y = .Cells(.Rows.Count, "K").End(xlUp).Row
End With

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1H4"
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues _
= Worksheets("SerialTable").Range("f2:F" & LastCellVRH1H4x) _
.Address(external:=True)
.SeriesCollection(1).Values _
= Worksheets("SerialTable").Range("k2:K" &
LastCellVRH1H4y).Value
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
'.PlotArea.Top = 18
'.PlotArea.Height = 162
'.Axes(xlValue).MaximumScale = 0.6
'.Deselect
End With
Application.ScreenUpdating = True
Sheets("Program").Activate
End Sub