Series

[Viktor Ygdorff]

My macro reads inputs from the user, uses this input to search a spreadsheet
for existing info and then update charts with the info from the spreadsheet.
It all works fine. My problem is that when updating the charts it is not
enough to simply write:

ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).Values =
Sheets(varWorksheetInfoArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
rng(k).Offset(j, 0).Address)

(do not care about the variables etc.) That is declareing the range for a
series does not automatically mean that you get that series. If e.g. the
chart already has a series and the x-axis and the y-series are not equally
long Excel presses data together so that giving new ranges to both x-axis and
y-series does not imply that these are the series that you actually get. Now
this causes some major problems for since I must let the user modify the
charts in order to e.g. evaluate different scenarios. However once the user
has done that my program is busted since the series that my program sets will
not necessarily be displayed in the chart. Is there any way of solving this?
I have tried with deleting existing series and then adding new ones again but
that gave me some headache also.. Thus if anyone has any clue about this
please help me out! Thank you very much. I am grateful for any help that I
get.

Ygdorff

 

[Bernie Deitrick]

Viktor,

I have found that an easy way to get new data into a chart reliably is to create the chart with a
known range, and then use a macro to delete the values in that range and write the new, desired
values into the range. Then you never need to change the seriescollection property....

HTH,
Bernie
MS Excel MVP

 

[Viktor Ygdorff]

Thank you Bernie! I am loosing my mind here.. I know that there are other
ways of doing this but it is too late to change my mode. The way that I do it
is that the charts and the series already exist. Now my macro reads input
dates from a user through a user form. It then uses these dates to search for
date ranges in different spreadsheets. These date ranges are the x-axis
values. The data is store in an array like this:

varWorksheetInfoArray(0) = "Invest Abs rel"
varWorksheetInfoArray(1) = "Date"
varWorksheetInfoArray(2) = "USD (rel)"
varWorksheetInfoArray(3) = "IEUR (rel)"

I then use the info from this array and put in a range array (so that i can
use the adresses).

Do While k < UBound(rng)
Set rng(k) =
Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(k),
LookIn:=xlValues)
k = k + 1
Loop

and then I assign the x-values (this works I think all the time)
ActiveChart.Axes(xlCategory).Select
With ActiveChart
.SeriesCollection(1).XValues =
Sheets(varWorksheetInfoArray(0)).Range(rng(1).Offset(i, 0).Address & ":" &
rng(1).Offset(j, 0).Address)
End With
it is now time for the y-values. they give a nice headache because they work
in the sense that I think the code is right but you have to do something with
the charts first. After having deleted and copied and pasted and what not it
works. So i guess the code is right but it might be missing something or
there is something specific that I need to do with the charts before being
able to apply my code. Anyway here is the code (it finds the right addresses
and everything:

k = 2 'start value
Do While k < (UBound(rng))
If Not IsEmpty(rng(k)) Then
ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).Values =
Sheets(varWorksheetInfoArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
rng(k).Offset(j, 0).Address)
Else: End If
k = k + 1
Loop

Now the thing is that sometime the series belong to a secondary axis. I have
tried recording a macro and using the code in the loop like this:

ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).AxisGroup = 1

I have tried putting the code in the beginning the end and on all possible
places but it does not work. I doubt that this has anything to do with it but
I cannot understand what you must do in order for it to work so.. Now is
there anything that I should add/replace/delete from my code for it to work.
I do not have the time to correct all the charts manually (there are about
100 charts and it takes me on average 15 minutes to get it to work since I do
not know the problem). Please I beg if anyone has any concrete tips or know
directly that there is something wrong etc. I would eternally grateful for
your help! This is really killing me and I do not know where to look in order
to learn something about it. As I said I know that there are probably other
ways of updating these charts but I cannot (for various reasons) write code
for all charts or something like that. I mean the code finds the correct
range for every series for every chart but it is (just) the problem of
applying these ranges to the charts. Again all help is appreciated!

Sincerely,

Viktor Ygdorff

"Bernie Deitrick" skrev:

 

[Viktor Ygdorff]

Hello Bernie! Well tried your solution and I did not get it to work. The
probelm is that the user must be able to rearrange the spreadsheet. Thus I
cannot directly refer to cells while recording the macro since there is no
"desired" range in terms of cell refernce. But I Think your on the right
track cause I have had some success with doing something similar manually but
I have not yet solved it. Thank you ve´ry much for your tip!

"Bernie Deitrick" skrev: