macro code for making a graph in excel Closed

Question

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?

Shandor · Accepted Answer

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. :)

Ivan-hoe · Answer

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

afrog4u2 · Answer

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?

Shandor · Answer

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?

Shandor · Answer

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

Shandor · Answer

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?

shandor · Answer

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?

thehowch · Answer

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

Neil A · Answer

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.

Macro code for making a graph in excel

9 responses

Similar discussions

Subscribe To Our Newsletter!