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]

This would do it:

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

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?

Don't worry about payment or anything - I'm free for now.

Plus the learning opportunity is kind of a payment in itself - I'd never have came up with that solution if it weren't for this thread, and now I know it for the future.

Anyway, I appreciate your gratitude.

As for learning, I'm a great believer in learning by experience. I learned in the following ways:

1) Recording macros and studying the resulting code; learning which parts of it were necessary and which weren't; condensing it; understanding the syntax.

2) Searching/asking for help on this forum.

3) Answering questions on this forum.

4) Being creative/ambitious - I've found that pretty much anything that can be conceived of logically is possible with VBA.

I think it's also really helpful to have things to work on, for example, I worked in Finance and had lots of uses for VBA there, e.g., tasks that would have otherwise been massively repetitive. I feel that that was the main reason I picked up VBA fairly quickly.

Another valuable asset is an already good understanding of worksheet formulas and functions, as these can translate pretty directly into VBA.

Hope this helps,

Chris.

 

[aikue]

It works! :') too beautiful. Thanks again!

I can see Finance being very repetitive and good motivation to learn VBA, lol.

I am still in school, halfway through engineering. I want to learn VBA because it looks pretty nice on a resume (lol) and it will help me out a lot down the road. Thank you for the tips !

 

[Chris Mack]

Yw.