Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series
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).Offs et(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series
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" wrote in message ... 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).Offs et(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series
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(va rWorksheetInfoArray(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).Offs et(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).Offs et(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, 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" wrote in message ... 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).Offs et(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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series
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: 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" wrote in message ... 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).Offs et(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Selectable Series and Number of Series for Line Chart | Charts and Charting in Excel | |||
Fill Series Dates: not letting me change the series from year to m | Excel Discussion (Misc queries) | |||
chart data series -- plot a table as a single series | Charts and Charting in Excel | |||
series graph -- one series being added to another series | Charts and Charting in Excel | |||
Filling in a Date Series using the Fill | Series menu command | Excel Programming |