Macro needed for multiple series of data to plot unique charts

**ehelliar** · 04-20-2011, 04:17 PM

Help!

My husband (jhelliar) is pretty good with excel but he says I am better with macros so can't help me.

I've got a macro which works to reproduce infinite numbers of charts based on a template, but the chart I now want to plot has 6 series of data.

The data is in rows, with 6 columns to one series. At the moment, my current macro is running the main data (exam results) to produce unique graphs, but series 2-6 is still coming from row 2. I need all the data to come from the same row.

Does this make sense?

I can attach a similar table if needs be, but the current one has lots of protected information in it (children's names and exam results etc).

Current macro I'm using is:

Please Login or Register  to view this content.

Can this one be edited?

**rman** · 04-20-2011, 04:29 PM

To me it seems like your row is defined here:

Please Login or Register  to view this content.

If this is the case, you need your 2 to change dynamically with n while still being 2 for your first data set. Am I correct?

If so, dim a new variable i and try this:

Please Login or Register  to view this content.

**ehelliar** · 04-20-2011, 04:32 PM

Thanks rman.

I thought that was going to be the problem area with the macro. It had to be the code rather than the template.

I'll edit the macro (and put in what you have written) and try that.

Thanks a lot

**ehelliar** · 04-20-2011, 04:35 PM

It's not happy.

As I've got data rows 122 - 150 sorted out (don't ask!) I've asked it to do charts 122 and 123, but it's trying to reproduce row 1 repeated times!

**rman** · 04-20-2011, 04:35 PM

oops, posted too soon......i'll try to dig more.

**ehelliar** · 04-20-2011, 04:39 PM

Thanks rman - I'm really grateful. I've been playing with it for the last couple of days and I can't figure it out!

E

**ehelliar** · 04-20-2011, 04:41 PM

It's this part that it doesn't like but I don't know why - it's never been a problem before!

Please Login or Register  to view this content.

OK - I've figured out why this line is a problem - it's trying to recreate the same student's chart over and over again, so would therefore have the same name. So that part of the code is OK but it's higher up with the numbering.

:S

**ehelliar** · 04-21-2011, 02:54 AM

Does anyone else have any ideas?

**Andy Pope** · 04-21-2011, 04:54 AM

You are applying the revised formula to series 1 every time.
You need to increment the series.

Please Login or Register  to view this content.

**ehelliar** · 04-21-2011, 09:14 AM

Ahhh great. Will try that

Thanks x

**ehelliar** · 04-21-2011, 10:08 AM

Sorry guys - it still doesn't like it

This is what I have got at the moment

Please Login or Register  to view this content.

**ehelliar** · 04-21-2011, 11:13 AM

I've tried fiddling with various parameter settings with the macro and I still can't get it to work.

Really getting frustrated now!!!

**Andy Pope** · 04-21-2011, 11:18 AM

post example workbook so we can see exactly what is going on.

**ehelliar** · 04-21-2011, 11:34 AM

Originally Posted by Andy Pope

post example workbook so we can see exactly what is going on.

Thanks Andy. I would be very grateful if you/someone/I could get this to work! I've removed all student's names so it should be OK (data protection act wise!)

E

**ehelliar** · 04-21-2011, 11:39 AM

The ones with the best data in are rows 122-150, so I'm putting 122 -123 as sn and fn in the input box.

**Andy Pope** · 04-21-2011, 11:48 AM

try this

Please Login or Register  to view this content.

**ehelliar** · 04-21-2011, 12:01 PM

Thanks,

It's happier but there's still an issue here:

Please Login or Register  to view this content.

**Andy Pope** · 04-21-2011, 12:14 PM

It's bombs when processing row 176 as there is not data. The copied chart ends up retaining the your name.

If your change the default value for the last student to process then it will work. It did students 1 to 174 for me.

**ehelliar** · 04-21-2011, 12:18 PM

Mine won't duplicate any due to the code issue:

Please Login or Register  to view this content.

Hmmmm

P.S. If I hover over objSeries it comes up with objSeries = nothing.

Is this right?

**ehelliar** · 04-21-2011, 12:29 PM

Sorry,

I think it's now working.

I restarted excel (as it crashed) and it seems happy

Thanks soooooo much for your help so far.

E

**ehelliar** · 04-21-2011, 12:41 PM

Spoke too soon.

It's still faulting on:

Please Login or Register  to view this content.

**ehelliar** · 04-21-2011, 01:00 PM

The charts are correct if I plot them individually (ie setting the input box to the same number) but it will NOT produce the next row down. I am closer Than I was as all data series are there but it's not perfect.

Sorry!

**ehelliar** · 04-21-2011, 03:40 PM

Originally Posted by ehelliar

Spoke too soon.

It's still faulting on:

Please Login or Register  to view this content.

I'm still at a loss as to why it won't work. I can sometimes get it to produce 3 graphs in a row, but at other times it won't even do 1.

It's still highlighting the above code as an error.

Any other ideas?

**ehelliar** · 04-22-2011, 08:51 AM

I am grateful for the help so far but I still can't get it to work properly.

Any ideas on what is still wrong?

**Andy Pope** · 04-22-2011, 10:04 AM

It must be something with your setup or file as it runs for me.

Do you actually need to produce individually charts?
have you considered using 1 chart with the ability, via formula, to see any students results.

**ehelliar** · 04-22-2011, 10:26 AM

Hi Andy,

Thanks for the reply - I am very grateful.

I need to print each chart, and have them available for teachers to look at and compare.

It does keep faulting on the same section, but if it works OK for you then I'll perservere! Reading a book on macros at the moment - see if I can get to the bottom of it . . .

Thanks,

E

**Andy Pope** · 04-22-2011, 10:57 AM

I'm wondering if all those non contigous range references are causing problems.

When I try the code in xl2010 it is a lot less stable.

Could you create chart only data that used contiguous ranges?

**ehelliar** · 04-22-2011, 11:11 AM

It might be but I haven't had a problem with it before in the other sheets (before it was amended - handling one series of data).

My HoD uses a similar document and it makes life LOTS easier to have all the data in the same format as his so that very few changes need to be made.

I thought, to begin with, that it was because I had filtered the data but that hasn't helped either.

It's still the same line faulting. What does that line mean?

**Andy Pope** · 04-23-2011, 07:00 AM

You only need to build a contigous range for the Student series, as you have already done the Marks.

See attached.

**ehelliar** · 04-23-2011, 07:01 AM

Ahhhh thanks Andy - I will try that.

**ehelliar** · 04-23-2011, 11:50 AM

Andy, thanks soooo much for your help.

Have got it working now

Macro needed for multiple series of data to plot unique charts

LinkBack

Thread Tools

Rate This Thread

Display

Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Re: Macro needed for multiple series of data to plot unique charts

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions