Add new series on a graph using a macro

[aikue]

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.

	B	AE	AF	AG	AH	AO
5		90	50	31.5	25	0
236	3-jul	55.9	7.7	3.4	2.1	0
237	11-jul	40.2	8.3	1	1	0
238	24-jul	84.9	20.3	3.6	3.1	0
239	30-jul	36.8	13.9	0.8	0.6	0

<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!!

 

[Chris Mack]

aikue,

I think you need to reference the .SeriesCollection() differently. When I substitute this 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:

[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]

The problem with this is that it will only act on the first series in a chart. The series just added might be the second or third series, etc.

Chris

 

[aikue]

Have a great weekend Chris!

Mickle, that macro does run without errors; however, when I select the date and click "run," a new series is created on the graph, always with the name 24-Jul, and without any x or y values. Interesting.

 

[Chris Mack]

Have a great weekend Chris!

Thanks!

Did you try my other idea?

If possible, you can upload your spreadsheet and I'll take a look.

 

[mrmmickle1]

aikue / Chris Mack,

Not sure what your data looks like aikue but, I would go with Chris Mack's alteration. I executed his procedure and it and it seems to run without errors. I think it's what you want to use. I am not familiar with .SeriesCollection so I wasn't sure what the (1) was doing, but I knew that this portion of that larger line of code: (Sheets("Cone Crusher PSD").Range("B238").Value) was causing the error. Good fix Chris!

 

[Chris Mack]

Thanks, good to know it's working.

Strange that my original SeriesCollection statement didn't work as it works in another macro I wrote. I'll have to check it out more and see what the difference is.

 

[aikue]

Sorry I disappeared for a while. Strange it worked for you Mickle; I still get error '1004' Invalid Parameter with Chris' modification:

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(CStr(ns.Name)).XValues = "={90,50,31.5,25}"
.SeriesCollection(CStr(ns.Name)).Values = "='Cone Crusher PSD'!$AE$238:$AO$238"
End With
End Sub

I still get the bug on ".SeriesCollection(CStr(ns.Name)).XValues = "={90,50,31.5,25}"

How would I upload an excel file?

 

[Chris Mack]

You can upload it to Box.com or something, then post the link here.

(It's free to make an account.)

Chris.

 

[aikue]

https://www.dropbox.com/s/01aghascggk9qf6/PSD Graphs.xlsm

I deleted all the cells except for the important few. I apologize for that; it's a bit confusing. The data I need starts in cell AA234 ish.

Thank you in advance for any help or advice.

 

[Chris Mack]

I'm confused as to why it didn't work, but here's a pretty good solution:

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

Hope this helps,

Chris.

 

[aikue]

Chris! That works so much better :D!

The only problem is that it only graphs the one date for Jul 24th, B238. When I click on another date, and apply the macro, it graphs July 24th's data "$AE$238:$AO$238", instead of the data on the new row.

Is there a way of selecting a new date, say cell B237, applying the macro, and adding a new series on the graph with data AE$237:$AO$237?

Thanks,

Is there any way I can repay you for your help? I appreciate the help tremendously. I really want to learn how to write macros myself. Do you know of any good online lessons or programs?


-Aikue