Macro code for making a graph in excel

Shandor - Oct 8, 2008 at 04:18 PM
 Neil A - Feb 4, 2011 at 10:01 AM

I would like to create a graph from a macro. Here is what I have got so far.

ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets(sheetname).Range("AF1:" & lastcell2, "BN1:" & lastcell), PlotBy:=xlColumns ****<------ something is wrong here

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Pressures"
Sheets(1).Move after:=Sheets(2)

ActiveChart.SeriesCollection(1).XValues = Sheets(sheetname).Range(("AL2"), lastcell3)
ActiveChart.SeriesCollection(2).XValues = Sheets(sheetname).Range(("AL2"), lastcell3)
ActiveChart.SeriesCollection(3).XValues = Sheets(sheetname).Range(("AL2"), lastcell3)
ActiveChart.SeriesCollection(4).XValues = Sheets(sheetname).Range(("AL2"), lastcell3)
ActiveChart.SeriesCollection(5).XValues = Sheets(sheetname).Range(("AL2"), lastcell3)

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Pressure During Test"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hours"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Pressure (psi)"
End With
ActiveChart.HasLegend = True
Selection.Position = xlBottom

I am managining wth the rest of this stuff but I marked a line of code with a ***. This line is giving me trouble. I only want to graph the data in column AF-AH and in BN and BO. But it seems to be selecting all the columns in between also.

What is the correct syntax to do this?

One more question, is there a way to get the x axis to be the data in column AL without it being part of the original selection and hwo do I just right the xvalues line only once instead of for each and every column of data?

9 responses

I'm sorry here is the code of a working graphing subroutine, not the one I am trying to get to work.
Private Sub GraphTempButton_Click()
' tempgraph2 Macro
' Macro recorded 9/29/2008 by Robert

Application.ScreenUpdating = False
FinalRow = Range("A65536").End(xlUp).Row
lastcell = Cells(FinalRow, "G").Address
lastcell2 = Cells(FinalRow, "AL").Address
sheetname = ActiveSheet.Name

ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets(sheetname).Range(("A1"), lastcell), PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Temperatures"
Sheets(1).Move after:=Sheets(2)

ActiveChart.SeriesCollection(1).XValues = Sheets(sheetname).Range(("AL2"), lastcell2)
ActiveChart.SeriesCollection(2).XValues = Sheets(sheetname).Range(("AL2"), lastcell2)
ActiveChart.SeriesCollection(3).XValues = Sheets(sheetname).Range(("AL2"), lastcell2)
ActiveChart.SeriesCollection(4).XValues = Sheets(sheetname).Range(("AL2"), lastcell2)
ActiveChart.SeriesCollection(5).XValues = Sheets(sheetname).Range(("AL2"), lastcell2)
ActiveChart.SeriesCollection(6).XValues = Sheets(sheetname).Range(("AL2"), lastcell2)

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Temperature During Test"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hours"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temp (C)"
End With
ActiveChart.HasLegend = True
Selection.Position = xlBottom

'now some text
Call PlaceGraphText
Application.ScreenUpdating = True
End Sub

Now you can see how I defined lastcell.

This works when all the data is in adjacent columns. I am trying to get something to work that does not have data in adjacent columns.

Further, I was hoping there was a single command that applies x axis data to all the y axis data. You can see in this example that I assign it 6 times.

Very little of this was from recording macros. I am brute forcing my way through macro programming by dong google searches for example macro code and trying to apply it. Some times I get stuck (like now). Ivan hoe usually shows me how simple it is and that I am being a dumbass. :)
THankyou I found your macro very helpful!
yeesh, sorry had to bump again, but I am still not finding this answer around on my own. If no one can answer this, does anyone have a good online resource for details on building graphs in excel with macros?
Ivan-hoe Posts 433 Registration date Saturday February 16, 2008 Status Member Last seen October 17, 2008 110
Oct 9, 2008 at 01:54 AM
Hello Shandor,
what is the type of your variables LastCell and LastCell2 ?
If they are set as range, you should write
ActiveChart.SetSourceData Source:= _ 
Sheets(sheetname).Range("AF1:" & LastCell2.Address, "BN1:" & LastCell.Address)

The easiest way I would do this is to use the Activechart.SeriesCollection.NewSeries method, i.e.:

Dim xlSheet as Excel.Worksheet
Dim xlChart as Excel.Chart
Set xlsheet = ActiveWorkbook.Sheets(i) 'Change I to whichever sheet it should be, ie which has the data
Set xlChart as Charts(Charts.Count) 'Selects the most recently created chart
With xlChart
.SeriesCollection.NewSeries 'If the selected cell is near data there will be existing series on the chart, so make sure the macro is called from a blank region
With .SeriesCollection(i) 'change I to what it should be, ie 1 if starting from blank
.Name = "" 'put in your series name here
.XValues = "=" & xlSheet.Name & "!R1C32:R" & xlSheet.Cells(1, 32).End(xlDown).Row & "C32"
.Values = "=" & xlSheet.Name & "!R1C33:R" & xlSheet.Cells(1, 33).End(xlDown).Row & "C34"
End With


I did your work for the first range, you should be able to figure out how to modify the above for your second range.

Good luck

Didn't find the answer you are looking for?

Ask a question
ivan hoe,

looks like that didnt work. Probably because lastcell is not defined as a range, but is instead a cell.

I get a 'missing object' error. Any ideas?
I should also add that I dont want the first column in my selection to appear as the x-axis. However if I can get the syntax of what I want to be working then I can just add the correct column as the first column and thos becomes less of a problem.

sorry about the bump. But I am still stuck. Does anyone at least have a good on-line resource I can use to figure out my graph issue?
I am new to this game, but looks like there are a few brackets missing on the line to me. All your range definitions eg A! are bracketted and quoted ("A1") thus. I think maybe you are short a few brackets here, but not sure where exactly.
afrog4u2 Posts 1 Registration date Wednesday October 8, 2008 Status Member Last seen October 9, 2008
Oct 9, 2008 at 06:09 AM
I agree that knowing the various variable types will help, but without knowing that and assuming that they are "STRING", that part of the code worked for me. What failed was when assigning the XValues.

ActiveChart.SeriesCollection(1).XValues = Sheets(sheetname).Range(("AL2"), lastcell3)

The error was "Run-time error '1004': Unable to set the XValues property of the Series Class.

How did you create this script? Did you record it when creating the chart originally?

Do you have any sample data you can give?