Picture is the best way to go. The 1004 error is probably due to the
length of the formula series exceeding 255 characters. A quick test of
this is to select the data series and then with the cursor in the
formula bar press F9. This will convert formula to values. If the
conversion is incomplete you will get an error.
Cheers
Andy
rmellison wrote:
Thanks for the continued help Andy, I have decided in the short term to use
the copy puicture method on Jon's website, but I will need to be able to edit
unlinked charts in the future so it'd be useful if we could nail this down.
I have, in an attempt to get it working first of all, cut my chart to down
to a single series of data with 200 x-values and 200 y-values, plotted as
scatter chart with no legend or axis titles. I have copied over the original
formulae for the data points with Paste Special | Values and deleted all
cells containing #N/A, but i've kept them in the range. I've also re-copied
the code from Jon's website into my VB editor (VBA Project
(Workbook)\Microsoft Excel Objects\ThisWorkbook in the navigation window on
the left).
Now when I run it from within excel, I get an error message saying 400 and
nothing else, with Ok and Help button but no Debug button. If I highlight the
chart and run from within VB editor I get "Run-time error '1004':
Application-defined or object-defined error". I am now utterly confused, and
still unsure as to which line of code is my nemesis!
Getting to the point where I say hang it all and just continue to use the
copy picture method, but it would be a useful macro to have working...
"Andy Pope" wrote:
I did test with NA() and blank cells both of these did not present a
problem.
Can you identify the actual line of code that produces the error? When
the error dialog comes up choose Debug. The offending line should be
hilited.
How much data do you have in terms of rows and columns?
Cheers
Andy
rmellison wrote:
Unfortunately can't email any data due to company restrictions. Spoil-sports!
If the problem is the wrong type of variable maybe I need to check the data
to make sure its in number format? Am I along the right lines? All of the
data points are generated by formulae; not sure if this is relevent. Also,
some of the data points are #N/A values, but I need to keep them in this form
so that the original chart uses 'best fit' values across null values, rather
than reverting to zero. Perhaps I need to modify the code to compensate for
#N/A values?
I also have a cell link for the series name (for use in the legend) which I
will also need to unlink. Is this relevent?
That's all I can think of really. As I said, still a bit of a novice with VBA.
Thanks for your help.
"Andy Pope" wrote:
Hi,
That's an error type 13, which suggests the wrong type of value is being
placed in a variable (eg. letter where a number is required).
I have tried various things to re create you problem but can not.
If you want email your workbook to me, off newsgroup, and I will take a
look.
Cheers
Andy
rmellison wrote:
Thanks Andy, Jon Peltier's site was very useful.
I'm using the macro for multiple series with lots of data, however I get an
error at line 13 (i think) saying type mismatch. I thought it may be because
my chart has scatter and line chart data on it, so I deleted the line data
and tried again, but got the same result. I'm somewhat of a novice at VBA,
can you help any further??
Error message in VB reads 'Runtime Error: '13': Type Mismatch'
"Andy Pope" wrote:
Hi,
Jon Peltier explains various way including the picture method.
http://peltiertech.com/Excel/ChartsH...ChartData.html
Cheers
Andy
rmellison wrote:
Is there a way of copyiong a chart from one workbook to another and
'unlinking' it, such that it does not update from the original worksheet if
the original data is changed. Does it need to be copied as a picture or
similar??
Thanks in advance.
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info