I want to make a graph with data from column A, B and J of a data table. The number of rows in the data will vary. I have tried the below code but get an error message on the range. Can anyone Help ?
Set cellGR1 = Cells(2, 1)
Set cellGR2 = Cells(K, 2)
Set cellGR3 = Cells(2, 10)
Set cellGR4 = Cells(K, 10)
Charts.Add
ActiveChart.ChartType = xlLine
PB is with below line :
ActiveChart.SetSourceData Source:=Sheets("Data").Range("GR1:GR2,GR3:GR4"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Dow Jones"""
ActiveChart.SeriesCollection(2).Name = "=""Total"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Total"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Chester White & Co"
End With
Application.Run "BLPLinkReset"
ActiveSheet.Shapes("Graphique 1").IncrementLeft -192.75
ActiveSheet.Shapes("Graphique 1").IncrementTop -143.25
ActiveSheet.Shapes("Graphique 1").ScaleWidth 2.02, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Graphique 1").ScaleHeight 1.55, msoFalse, _
msoScaleFromTopLeft
Windows("Graphique Total 22 juillet 08.xls").ScrollColumn = 3
Windows("Graphique Total 22 juillet 08.xls").ScrollColumn = 2
Windows("Graphique Total 22 juillet 08.xls").ScrollColumn = 1
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.NumberFormat = "0"
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = 45
End With
Set cellGR1 = Cells(2, 1)
Set cellGR2 = Cells(K, 2)
Set cellGR3 = Cells(2, 10)
Set cellGR4 = Cells(K, 10)
Charts.Add
ActiveChart.ChartType = xlLine
PB is with below line :
ActiveChart.SetSourceData Source:=Sheets("Data").Range("GR1:GR2,GR3:GR4"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Dow Jones"""
ActiveChart.SeriesCollection(2).Name = "=""Total"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Total"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Chester White & Co"
End With
Application.Run "BLPLinkReset"
ActiveSheet.Shapes("Graphique 1").IncrementLeft -192.75
ActiveSheet.Shapes("Graphique 1").IncrementTop -143.25
ActiveSheet.Shapes("Graphique 1").ScaleWidth 2.02, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Graphique 1").ScaleHeight 1.55, msoFalse, _
msoScaleFromTopLeft
Windows("Graphique Total 22 juillet 08.xls").ScrollColumn = 3
Windows("Graphique Total 22 juillet 08.xls").ScrollColumn = 2
Windows("Graphique Total 22 juillet 08.xls").ScrollColumn = 1
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.NumberFormat = "0"
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = 45
End With