Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Chart Source Data
I used the "Record Macro" function to get the code shown below. When
I recorded the macro, the value of A1 was 392. But when I actually ran the macro, the value of A1 was 393. As you can see, the "Record Macro" function "hard coded" the value of 392. How can I make this work such that the SeriesCollection is updated with the CURRENT value of A1? Sheets("Chart2").Select Range("A1").Select Selection.Copy Sheets("Chart1").Select ActiveChart.PlotArea.Select Application.CutCopyMode = False ActiveChart.SeriesCollection(1).XValues = "='Approval Index'! R2C1:R392C1" ActiveChart.SeriesCollection(1).Values = "='Approval Index'! R2C5:R392C5" ActiveChart.SeriesCollection(2).XValues = "='Approval Index'! R2C1:R392C1" ActiveChart.SeriesCollection(2).Values = "='Approval Index'! R2C7:R392C7" Sheets("Data Input").Select Range("A13").Select Thanks for your help... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Chart Source Data
You can make your Series update without code using a 'Dynamic Range Name'
Looks like your X/category values are in 'Approval Index'!A1:A# Series1-Y values offset in col-E Series2-Y values offset in Col-G where # is the value in A1. Define the following names, myCat =OFFSET('Approval Index'!$A$1,1,0,'Approval Index'!$A$1-1) mySeries1 =OFFSET(myCat,,4) mySeries2 =OFFSET(myCat,,6) If A1 is not on 'Approval Index' change the sheet name accordingly Change your Series1 & 2 formulas to =SERIES(,Book1.xls!myCat,Book1.xls!mySeries1,1) =SERIES(,Book1.xls!myCat,Book1.xls!mySeries2,2) Change the workbook name to suit Regards, Peter T "iamnu" wrote in message ... I used the "Record Macro" function to get the code shown below. When I recorded the macro, the value of A1 was 392. But when I actually ran the macro, the value of A1 was 393. As you can see, the "Record Macro" function "hard coded" the value of 392. How can I make this work such that the SeriesCollection is updated with the CURRENT value of A1? Sheets("Chart2").Select Range("A1").Select Selection.Copy Sheets("Chart1").Select ActiveChart.PlotArea.Select Application.CutCopyMode = False ActiveChart.SeriesCollection(1).XValues = "='Approval Index'! R2C1:R392C1" ActiveChart.SeriesCollection(1).Values = "='Approval Index'! R2C5:R392C5" ActiveChart.SeriesCollection(2).XValues = "='Approval Index'! R2C1:R392C1" ActiveChart.SeriesCollection(2).Values = "='Approval Index'! R2C7:R392C7" Sheets("Data Input").Select Range("A13").Select Thanks for your help... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Chart Source Data
I like crete data ranges the using the Address properties to get the R1C1 addresses. with Sheets("Approval Index") .LastRow = .Range("A" & Rows.count).end(xlup).row Sheets("Chart1").Select Set ChartLabels = .Range("A2:A" & LastRow) ChartLabelsAddr = ChartLabels.address( _ External:=true,ReferenceStyle:=xlR1C1) Set Series1 = .Range("E2:E" & LastRow) Series1Addr = Series1.address(External:=true,ReferenceStyle:=xlR 1C1) Set Series2 = .Range("G2:G" & LastRow) Series2Addr = Series1.address(External:=true,ReferenceStyle:=xlR 1C1) end with set MyChart = Sheets("Chart1") Mychart.Select ActiveChart.ChartArea.Select with ActiveChart .SeriesCollection(1).XValues = "=" & ChartLabelsAddr .SeriesCollection(1).Values = "=' & Series1Addr .SeriesCollection(2).XValues = "=" & ChartLabelsAddr .SeriesCollection(2).Values = "=' & Series2Addr end with -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165255 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Chart Source Data
Typo
Looks like your X/category values are in 'Approval Index'!A1:A# should be Looks like your X/category values are in 'Approval Index'!A2:A# Peter T "Peter T" <peter_t@discussions wrote in message ... You can make your Series update without code using a 'Dynamic Range Name' Looks like your X/category values are in 'Approval Index'!A1:A# Series1-Y values offset in col-E Series2-Y values offset in Col-G where # is the value in A1. Define the following names, myCat =OFFSET('Approval Index'!$A$1,1,0,'Approval Index'!$A$1-1) mySeries1 =OFFSET(myCat,,4) mySeries2 =OFFSET(myCat,,6) If A1 is not on 'Approval Index' change the sheet name accordingly Change your Series1 & 2 formulas to =SERIES(,Book1.xls!myCat,Book1.xls!mySeries1,1) =SERIES(,Book1.xls!myCat,Book1.xls!mySeries2,2) Change the workbook name to suit Regards, Peter T "iamnu" wrote in message ... I used the "Record Macro" function to get the code shown below. When I recorded the macro, the value of A1 was 392. But when I actually ran the macro, the value of A1 was 393. As you can see, the "Record Macro" function "hard coded" the value of 392. How can I make this work such that the SeriesCollection is updated with the CURRENT value of A1? Sheets("Chart2").Select Range("A1").Select Selection.Copy Sheets("Chart1").Select ActiveChart.PlotArea.Select Application.CutCopyMode = False ActiveChart.SeriesCollection(1).XValues = "='Approval Index'! R2C1:R392C1" ActiveChart.SeriesCollection(1).Values = "='Approval Index'! R2C5:R392C5" ActiveChart.SeriesCollection(2).XValues = "='Approval Index'! R2C1:R392C1" ActiveChart.SeriesCollection(2).Values = "='Approval Index'! R2C7:R392C7" Sheets("Data Input").Select Range("A13").Select Thanks for your help... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA to update Chart Source Data Rage on Multiple Charts | Excel Programming | |||
Excel 2007 - How do I quickly update a chart using Source data? | Charts and Charting in Excel | |||
Update a chart immediately after inputting data into data source | Charts and Charting in Excel | |||
Chart/Source Data update problem | Charts and Charting in Excel | |||
How do I automatically update chart source data links for moved fi | Charts and Charting in Excel |