Graphics with VBA pb with data range

lafrenie

Board Regular
Joined
May 30, 2007
Messages
60
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,108
Office Version
  1. 365
Platform
  1. Windows
Didn't the recorder complain when you tried that?

Try:
Code:
     ActiveChart.SetSourceData Source:=Sheets("Data").Range("GR1:GR4"), _
        PlotBy:=xlColumns
 

lafrenie

Board Regular
Joined
May 30, 2007
Messages
60
This selects the entire table, I only want Column A and B and I. Can anybody help ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,595
Messages
5,597,078
Members
414,121
Latest member
DamianX88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top