Add new series on a graph using a macro

aikue

New Member
Joined
Aug 8, 2014
Messages
10
Hello everyone. I am trying to create a macro. I am very inexperienced and my knowledge of coding is very minimal. I would be very grateful if anyone could help me out. Thank you :)

This is a copy of my table (bold are the rows and coloums). The workbook is called "PSD Graphs". The Sheet is called "Cone Crusher PSD."

I want to enter data on a weekly basis, adding more rows as the year progresses. I want to make a macro so I can select a date and add its row of data onto my graph.

BAEAFAGAHAO
5905031.5250
2363-jul55.97.73.42.10
23711-jul40.28.3110
23824-jul84.920.33.63.10
23930-jul36.813.90.80.60

<tbody>
</tbody>

I tried recording a macro and this is what I get:

Sub Macro11()
'
' Macro11 Macro
'
' Keyboard Shortcut: Ctrl+m
'
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Cone Crusher PSD'!$B$238"
ActiveChart.SeriesCollection(2).XValues = "={90,50,31.5,25}"
ActiveChart.SeriesCollection(2).Values = "='Cone Crusher PSD'!$AE$238:$AO$238"
End Sub


When I select a date and try to apply my macro, it adds a blank series to my graph.

Thank you again for any help!!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Perhaps try:

Code:
Sub Macro11()
'
' Macro11 Macro
'
' Keyboard Shortcut: Ctrl+m
'
With ActiveSheet.ChartObjects("Chart 5")
Set ns = .SeriesCollection.NewSeries
ns.Name = "='Cone Crusher PSD'!$B$238"
ActiveChart.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).XValues = "={90,50,31.5,25}"
ActiveChart.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).Values = "='Cone Crusher PSD'!$AE$238:$AO$238"
End With
End Sub
The code may condense a bit more if you try different things but this syntax has worked for me.

Hope this helps,

Chris.
 
Last edited:
Upvote 0
Thank you for taking a look at my code. Unfortunately when I try running the macro, nothing happens. I tried clicking debug and this statement is highlighted yellow: "Set ns = .SeriesCollection.NewSeries".

I wish I knew what that means :P!
 
Upvote 0
An amendment:

Rich (BB code):
Sub Macro11()
'
' Macro11 Macro
'
' Keyboard Shortcut: Ctrl+m
'
With ActiveSheet.ChartObjects("Chart 5").Chart
Set ns = .SeriesCollection.NewSeries
ns.Name = "='Cone Crusher PSD'!$B$238"
ActiveChart.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).XValues = "={90,50,31.5,25}"
ActiveChart.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).Values = "='Cone Crusher PSD'!$AE$238:$AO$238"
End With
End Sub
Works now?

Chris
 
Upvote 0
Run-time error '91'
Object variable or With block variable not set


"ActiveChart.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).XValues = "={90,50,31.5,25}"
 
Upvote 0
Try the array like this:

Code:
[COLOR=#333333]ActiveChart.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).XValues = Array(90,50,31.5,25)[/COLOR]

Reference Website
 
Last edited:
Upvote 0
A second amendment:

Code:
Sub Macro11()
'
' Macro11 Macro
'
' Keyboard Shortcut: Ctrl+m
'
With ActiveSheet.ChartObjects("Chart 5").Chart
Set ns = .SeriesCollection.NewSeries
ns.Name = "='Cone Crusher PSD'!$B$238"
.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).XValues = "={90,50,31.5,25}"
.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).Values = "='Cone Crusher PSD'!$AE$238:$AO$238"
End With
End Sub
Chris
 
Upvote 0
I get error 91 again with mickle's change on the same line of code
With the second amendment, Chris, I get Run-time error '1004' Invalid Parameter on the same line of code.

Thanks again for all the help
 
Upvote 0
I'm off work for the weekend now, so won't be able to experiment further until Monday.

For now, perhaps try replacing each instance of...

Code:
SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value)
... with...

Code:
SeriesCollection(CStr(ns.Name))
Chris
 
Upvote 0
aikue,

I think you need to reference the .SeriesCollection() differently. When I substitute this code:
Code:
[COLOR=#0000ff]Sub[/COLOR] Macro11()
[COLOR=#008000]'
' Macro11 Macro
'
' Keyboard Shortcut: Ctrl+m
'[/COLOR]
[COLOR=#0000ff]With [/COLOR]ActiveSheet.ChartObjects("Chart 5").Chart
[COLOR=#0000ff]Set[/COLOR] ns = .SeriesCollection.NewSeries
ns.Name = "='Cone Crusher PSD'!$B$238"
.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).XValues = "={90,50,31.5,25}"
.SeriesCollection(Sheets("Cone Crusher PSD").Range("B238").Value).Values = "='Cone Crusher PSD'!$AE$238:$AO$238"
[COLOR=#0000ff]End With
End Sub[/COLOR]

For the below code. The procedure runs without errors:

Code:
[COLOR=#0000ff]Sub [/COLOR]Macro11()


[COLOR=#0000ff]     With [/COLOR]ActiveSheet.ChartObjects("Chart 5").Chart
   [COLOR=#0000ff]      Set[/COLOR] ns = .SeriesCollection.NewSeries
         ns.Name = "='Cone Crusher PSD'!$B$238"
         .SeriesCollection(1).XValues = Array(90, 50, 31.5, 25)
         .SeriesCollection(1).Values = "='Cone Crusher PSD'!$AE$238:$AO$238"
[COLOR=#0000ff]     End With[/COLOR]


[COLOR=#0000ff]End Sub[/COLOR]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top