Macro code for making a graph in excel [Closed]

- - Latest reply:  Neil A - Feb 4, 2011 at 10:01 AM
Hello,

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

Charts.Add
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
ActiveChart.Legend.Select
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?
See more 

9 replies

Best answer
18
Thank you
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

Charts.Add
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
ActiveChart.Legend.Select
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. :)

Say "Thank you" 18

A few words of thanks would be greatly appreciated. Add comment

CCM 5687 users have said thank you to us this month

THankyou I found your macro very helpful!
9
Thank you
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?
Posts
436
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
108
4
Thank you
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)

Ivan
3
Thank you
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
Charts.Add
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
.SeriesCollection.NewSeries

etc.

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
1
Thank you
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.

Thanks
0
Thank you
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?
0
Thank you
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.
Posts
1
Registration date
Wednesday October 8, 2008
Status
Member
Last seen
October 9, 2008
-1
Thank you
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?
-1
Thank you
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?