Macro to generate charts, not working correctly.

Ahhh! I've been pulling my hair out over that problem for ages, thanks andy - works perfectly.

Andy sorry for asking this on the same post and not starting a new one but the section of code labelled 'chart title = x'. I wasn't sure how to make this correspond to the dataset.
Basically titles of each data range are on A4,A204,A404 etc. etc. How can I make the title match that specific rule.

Originally Posted by Andy Pope

You can use the offset function to get to the cell

You may need to alter the number of rows, -3, to offset.

Please Login or Register  to view this content.

Does that mean I can't put it where it previously was, as it wouldn't work under activechart any more would it. Where should I move it to?

Originally Posted by Andy Pope

no need to move it. Only change is the "x" to using the range reference.

Sorry I'm probably missing something obvious but it causes a syntax error when I try and run it.

Check out this attachment. It contains only 3 data ranges, but the real one has about 400. Ignore all the working out I've done the important cells are the ones highlighted orange.
If you can't see any of the values let me know - I'm getting them from an external program.

Originally Posted by Andy Pope

extra bracket was causing the syntax error.

for the first pass through the loop this will reference A5. If it should be A1 then change -3 to -7

Please Login or Register  to view this content.

Hard to tell what it should reference as the cells are all #NAME? errors.

You can only see the values if you have Bloomberg. But the data range runs from A8:A43, A208:A243 etc. And the titles are A4, A204 etc. etc,.

Yeah I was just about to say, changed the offset value to -4 and it works.
Thanks so much for the help andy,
Alex

Andy just one last thing, if I was to generate charts just my data was listed down a column. B1/C1/D1 etc etc. would have the title of the data set. And B2:B37, C2:C37, D2:D37 etc etc. would contain the data range. Also the fixed set of data (i.e the y-axis) was in A2:A37.

I changed this part of my macro:

Please Login or Register  to view this content.

What i've done doesn't work however, could you let me know why if possible.
Thanks.

Yep, works perfectly.
Cheers.

Sorry Andy just realized I've done another thing wrong:

Please Login or Register  to view this content.

I meant for it to colour the bars, not the text in the cells: I've been trying to figure it out - but I'm fairly bad at charting on VBA, what should it actually read?

Andy I think this would work if the charts are on the same spreadsheet as the data but they aren't, am I right in thinking this?

Just because when I run the script after I've generated the charts... it doesn't work. Am I doing something obvious wrong?
Cheers.

I had 'Activechart' Instead of 'Activesheet' my bad.
Sorry how would I redefine the title for this new format? (So titles are in B2,C2,D2 etc etc.)
Cheers.

Also Andy,
Your macro on that document you posted is defined for only one chart, If I wanted this to colour multiple charts how would I?
Cheers,
Alex.

Originally Posted by alocke

Also Andy,
Your macro on that document you posted is defined for only one chart, If I wanted this to colour multiple charts how would I?
Cheers,
Alex.

Sorry that was simple, I've managed to solve that problem.

Sorry to ask again but I can't seem to get the title on the new dataset working.
currently I have Offset(-1,1) [I think] But it just gives one title for every dataset. (Titles are in B1,C1 etc etc.)
Cheers.

So heres an example of what I mean. (See attachment)
-x,y,z are titles.
-B2:B37, data for the bar graph of the chart
-B39:B74 data for the line graph of the chart

How would I change my script so that:
1) Values B39:B74 are included in the chart as well, as a line graph on the already existant bar graph.
2) titles x,y and z are made the chart titles.
3) #N/A values are ignored from the chart.

Cheers.

Hey, so it pretty much works except the linegraph corresponds to the same data as the bar graph.
- If you see on the workbook I posted, the linegraph data is the data under the barchart data (Cells 39 - 74) or should the macro already do this?

Cheers.

Also how would you display the second x axis... Mine isn't appearing when I run the script?
Cheers.

Hi andy, sorry I didn't acknowledge this post at the time of you posting it!
I hope this doesn't go against the forum rules, but I thought it would be easier to relate back to if I didn't post it as a new thread.

The macro works perfectly btw. But I was wondering if I were to have say, 300+ charts generated is there a bit of code I can include to make all the charts visible. What I mean by this is, upon making the charts they all appear one on top of the other, and instead of moving them manually Id like to get a bit of code to do that. Is there a quick fix to this? I was thinking if each chart was made into an object and moved into a word document it could work - or is there amore simple way to do this.

Thanks in advance!