VBA creating chart and set data range

[ajm1991]

the pasted code works for the first two pie charts but it gets caught on the third
on this line ActiveChart.SeriesCollection(1).Values = datarange2
any advice?

Please Login or Register  to view this content.

[judgeh59]

Hello;

so, "gets caught" can mean a bunch of things....do you get an error?...does the macro actually hang and you have to close XL?....

[ajm1991]

Hi Judgeh,

Runtime error: Invalid Parameter
A blank chart is created with no data.
I appreciate the help.

[judgeh59]

my guess would be that either variable "d" or "f" are not valid for the range...without see the data I'm just guessing...

[ajm1991]

what do you mean by "not valid for the range"?
When I run line by line d is 63 and f is 8

[judgeh59]

sorry, just wondering if the range(Cells(63,18),Cell(63,25)) is a valid range for the chart....can you upload a file with no company confidential info?...

[ajm1991]

Thanks for looking into this!

[judgeh59]

this seems to work...

I added a line that chose a field in your dataset. I looked like the first 2 passes it was pick up data when you created the chart and then "changed" the dataset. BUT, the 3rd time it didn't seem to have a dataset, so I told XL to chose the dataset where the current cell was and that seem to work....it's definitely not my preferred method....

hope this helps....

Please Login or Register  to view this content.

[ajm1991]

hmmm weird. how did you figure out that was the issue?

[judgeh59]

I stepped through the code (F8) and watch how everything was being created....I noticed that the 1st 2 times after the Chart.add the chart was created using data but the 3rd time, no data....so, knowing that XL will attempt to create a chart with a dataset using a single cell and contiguous data, I forced it....