Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Multiple chart source data
I have 200 charts (7 on each page)with one data page. I'd like to be
able to loop through the pages and charts and the row addresses. My goal is to be able to change the column address from "f" to say "h" for example, and have the macro loop through all the pages, charts and range addresses. I've figured out how to loop through activating the page and the charts on each page, but can't figure out how to express the range statement .Range("A2:f6"), with variables so I can loop through the row values for each chart. I've tried statements like this -- but no luck. Am I just getting the syntax wrong? ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A" & "z" & ":" & "F" & "y"), _ PlotBy:=xlRows I would like to loop rather than having 200 statement like this since the column names are the same throughout and the row numbers increment by 7. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A2:f6"), _ PlotBy:=xlRows ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A9:f13"), _ PlotBy:=xlRows ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A16:f20"), _ PlotBy:=xlRows Your help will be appreciated. Thank You. Renate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Multiple chart source data
Possibly you could loop through the seriescollection of each chart and do
something like: ActiveChart.SeriesCollection(1).Formula = Application.Substitute( _ ActiveChart.SeriesCollection(1).Formula,"$F","$H") The results of checking the formula property is ? ActiveChart.SeriesCollection(1).Formula =SERIES('[aa_book1.xls]Sheet1'!$F$1,,'[aa_book1.xls]Sheet1'!$F$2:$F$14,1) -- Regards, Tom Ogilvy "Renate Stach" wrote in message om... I have 200 charts (7 on each page)with one data page. I'd like to be able to loop through the pages and charts and the row addresses. My goal is to be able to change the column address from "f" to say "h" for example, and have the macro loop through all the pages, charts and range addresses. I've figured out how to loop through activating the page and the charts on each page, but can't figure out how to express the range statement .Range("A2:f6"), with variables so I can loop through the row values for each chart. I've tried statements like this -- but no luck. Am I just getting the syntax wrong? ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A" & "z" & ":" & "F" & "y"), _ PlotBy:=xlRows I would like to loop rather than having 200 statement like this since the column names are the same throughout and the row numbers increment by 7. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A2:f6"), _ PlotBy:=xlRows ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A9:f13"), _ PlotBy:=xlRows ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A16:f20"), _ PlotBy:=xlRows Your help will be appreciated. Thank You. Renate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Multiple chart source data
Tom,
Thanks you so much!!!! This will save me tons of time. "Tom Ogilvy" wrote in message ... Possibly you could loop through the seriescollection of each chart and do something like: ActiveChart.SeriesCollection(1).Formula = Application.Substitute( _ ActiveChart.SeriesCollection(1).Formula,"$F","$H") The results of checking the formula property is ? ActiveChart.SeriesCollection(1).Formula =SERIES('[aa_book1.xls]Sheet1'!$F$1,,'[aa_book1.xls]Sheet1'!$F$2:$F$14,1) -- Regards, Tom Ogilvy "Renate Stach" wrote in message om... I have 200 charts (7 on each page)with one data page. I'd like to be able to loop through the pages and charts and the row addresses. My goal is to be able to change the column address from "f" to say "h" for example, and have the macro loop through all the pages, charts and range addresses. I've figured out how to loop through activating the page and the charts on each page, but can't figure out how to express the range statement .Range("A2:f6"), with variables so I can loop through the row values for each chart. I've tried statements like this -- but no luck. Am I just getting the syntax wrong? ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A" & "z" & ":" & "F" & "y"), _ PlotBy:=xlRows I would like to loop rather than having 200 statement like this since the column names are the same throughout and the row numbers increment by 7. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A2:f6"), _ PlotBy:=xlRows ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A9:f13"), _ PlotBy:=xlRows ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet23").Range("A16:f20"), _ PlotBy:=xlRows Your help will be appreciated. Thank You. Renate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Multiple chart source data
This is the mechanism behind the Change Series Formula macro I've posted
on my web site: http://peltiertech.com/Excel/Charts/ChgSrsFmla.html I have a few choices built into it: Active Chart vs. All Charts on Sheet. Another For-Next loop around that will get all charts on all sheets. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tom Ogilvy wrote: Possibly you could loop through the seriescollection of each chart and do something like: ActiveChart.SeriesCollection(1).Formula = Application.Substitute( _ ActiveChart.SeriesCollection(1).Formula,"$F","$H") The results of checking the formula property is ? ActiveChart.SeriesCollection(1).Formula =SERIES('[aa_book1.xls]Sheet1'!$F$1,,'[aa_book1.xls]Sheet1'!$F$2:$F$14,1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Source Data in Chart | Charts and Charting in Excel | |||
Change source data of a pivot chart | Charts and Charting in Excel | |||
Edit Chart to Change Source Data | Charts and Charting in Excel | |||
Change source data in a pivot chart | Charts and Charting in Excel | |||
change chart source from range to raw data | Excel Programming |