Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Chart Range
I'm trying to dynamically change the data range for a line chart
either in the chart setup or through VBA. I first tried via the chart wizard - I defined a range name and by using an OFFSET function in the "refers to" made it dynamic. I couldn't figure out how to get the wizard to accept a range name rather than a set data range. I've done dynamic data for pivot tables in VBA so I tried that. For pivot tables, I can make those dynamic by using "name.add" and then referring to a string in a cell that I've built that defines the range boundaries. Not that elegant and looking for something simplier if possible. In VBA, I also tried setting a range (ie, SET rng1 = range(cells(1,1),cells(i,1)) then in the activechart syntax tried to feed it range:= rng1, etc. but couldn't get that to work either. I don't want to define the chart range as "$A$2:$A$65536" and use "#N/A" in the blank fields to trick the graph into not showing the data - not that elegant either. Any ideas? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Chart Range
Chuck, See if there is anything here that can help you: http://www.tushar-mehta.com/excel/ne...ynamic_charts/ http://peltiertech.com/Excel/Charts/Dynamics.html Does that help? Dave Chuck Taylor Wrote: I'm trying to dynamically change the data range for a line chart either in the chart setup or through VBA. I first tried via the chart wizard - I defined a range name and by using an OFFSET function in the "refers to" made it dynamic. I couldn't figure out how to get the wizard to accept a range name rather than a set data range. I've done dynamic data for pivot tables in VBA so I tried that. For pivot tables, I can make those dynamic by using "name.add" and then referring to a string in a cell that I've built that defines the range boundaries. Not that elegant and looking for something simplier if possible. In VBA, I also tried setting a range (ie, SET rng1 = range(cells(1,1),cells(i,1)) then in the activechart syntax tried to feed it range:= rng1, etc. but couldn't get that to work either. I don't want to define the chart range as "$A$2:$A$65536" and use "#N/A" in the blank fields to trick the graph into not showing the data - not that elegant either. Any ideas? Thanks. -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=389668 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Chart Range
I'll asume that you created the dynamic named range through
InsertNameDefine and used the "Refers to" window in this dialog. To ensure that it is working correctly, I usually click inside the "Refers to" window and check to see that it highlights the correct range. Assuming the range name is "MyData", in the Series tab of the Chart Wizard, define the values for the Line chart as follows: =XYZ.xls!MyData where XYZ.xls is the name of the workbook. To create a dynamic named range with VBA try this: Dim Nm As Name Dim txt As String txt = "=Offset(Sheet2!$C$1, 0, 0, Count(Sheet2!$C:$C), 1)" Set Nm = ThisWorkbook.Names.Add("MyData", txt) Regards, Greg "Chuck Taylor" wrote: I'm trying to dynamically change the data range for a line chart either in the chart setup or through VBA. I first tried via the chart wizard - I defined a range name and by using an OFFSET function in the "refers to" made it dynamic. I couldn't figure out how to get the wizard to accept a range name rather than a set data range. I've done dynamic data for pivot tables in VBA so I tried that. For pivot tables, I can make those dynamic by using "name.add" and then referring to a string in a cell that I've built that defines the range boundaries. Not that elegant and looking for something simplier if possible. In VBA, I also tried setting a range (ie, SET rng1 = range(cells(1,1),cells(i,1)) then in the activechart syntax tried to feed it range:= rng1, etc. but couldn't get that to work either. I don't want to define the chart range as "$A$2:$A$65536" and use "#N/A" in the blank fields to trick the graph into not showing the data - not that elegant either. Any ideas? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic range and chart - big help!! | Excel Worksheet Functions | |||
Chart empty with new data in the dynamic range chart. | Excel Discussion (Misc queries) | |||
Dynamic range in chart | Charts and Charting in Excel | |||
Dynamic Range Chart ??? | Charts and Charting in Excel | |||
Dynamic Chart Range and Chart Update | Excel Programming |