Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Charting with dynamic data
All,
I am creating a chart which is based on multiple columns worth of data. The problem is that the amount of rows changes and I have to be able to handle that programmaticaly. In other words, if the amount of rows was constant somewhere in my sub I would have something like: ..Chart.SetSourceData Source:=Sheets("NameGoesHere").Range("A2:A5, F2:H5") 'where the Range A2:A5 contains names that goes to label the X-axis. 'And F2:H5 represents the data for each name in A2:A5. No problem. However, I need that range to vary. I was hoping something like this would work: .Chart.ChartWizard Source:=Worksheets(xlSheet(I).Name).Range(xlSheet( I).Cells(2, 1):xlSheet(I).Cells(z, 1), xlSheet(I).Cells(2, 6):xlSheet(I).Cells(z, 8)) 'where z is an integer which calculated the number of rows needed. Problem. 'Didn't work As you can see I tried to mimic the form used for static data but failed miserably. Any help would be greatly appreciated. Clear as mud? Thanks in advance. -- J |
#2
|
|||
|
|||
Hi Jon,
I would try another procedu Link the chart with a named range, e.g. ..Chart.SetSourceData Source:=Sheets("NameGoesHere").Range ("arguments") Then you can name the arguments range, starting at a known cell e.g. A2 and down all filled rows with: ..Range("A2", "A" & .Range("A2", "A65534").SpecialCells (xlCellTypeBlanks).Cells(1).Row - 1).Name = "data" For info: xlCellTypeBlanks gives you the first empty cell, therefore you need to use .Row - 1 Best Markus |
#3
|
|||
|
|||
Markus,
Thanks for the quick response. I see exactly what you are trying, but I cannot get my code to run with it. Here's the relevant snippet, tell me if this is what you had in mind. Set ch = Worksheets("Charts").ChartObjects.Add(Left:=15, Width:=700, _ Top:=(J * (225 + 25) + 50), Height:=225) With ch .Chart.SetSourceData Source:=Sheets(xlSheet(I).Name).Range(xlSheet(I). _ Cells(2, 6), xlSheet(I).Cells(z, 8)) .Chart.Range("A2", "A" & .Range("A2", "A65534").SpecialCells _ (xlCellTypeBlanks).Cells(1).Row - 1).Name = "data" End With I end up with a run-time error 438 "Object doesn't support this property or method" What am I missing here? "Markus Scheible" wrote: Hi Jon, I would try another procedu Link the chart with a named range, e.g. ..Chart.SetSourceData Source:=Sheets("NameGoesHere").Range ("arguments") Then you can name the arguments range, starting at a known cell e.g. A2 and down all filled rows with: ..Range("A2", "A" & .Range("A2", "A65534").SpecialCells (xlCellTypeBlanks).Cells(1).Row - 1).Name = "data" For info: xlCellTypeBlanks gives you the first empty cell, therefore you need to use .Row - 1 Best Markus |
#4
|
|||
|
|||
Anyone else have thoughts on this?
"Jon" wrote: All, I am creating a chart which is based on multiple columns worth of data. The problem is that the amount of rows changes and I have to be able to handle that programmaticaly. In other words, if the amount of rows was constant somewhere in my sub I would have something like: .Chart.SetSourceData Source:=Sheets("NameGoesHere").Range("A2:A5, F2:H5") 'where the Range A2:A5 contains names that goes to label the X-axis. 'And F2:H5 represents the data for each name in A2:A5. No problem. However, I need that range to vary. I was hoping something like this would work: .Chart.ChartWizard Source:=Worksheets(xlSheet(I).Name).Range(xlSheet( I).Cells(2, 1):xlSheet(I).Cells(z, 1), xlSheet(I).Cells(2, 6):xlSheet(I).Cells(z, 8)) 'where z is an integer which calculated the number of rows needed. Problem. 'Didn't work As you can see I tried to mimic the form used for static data but failed miserably. Any help would be greatly appreciated. Clear as mud? Thanks in advance. -- J |
#5
|
|||
|
|||
For all who are interested here is a snippet that provides the solution (I
have no hair now...). UNION was the key. With ch ..Chart.ChartWizard Source:=Union( _ Worksheets(xlSheet(i).Name).Range(xlSheet(i).Cells (2, 1), xlSheet(i).Cells(z, 1)), _ Worksheets(xlSheet(i).Name).Range(xlSheet(i).Cells (2, 6), xlSheet(i).Cells(z, 8))), _ Title:=xlSheet(i).Name, _ PlotBy:=xlColumns, _ CategoryLabels:=1 End With Thanks to all who thought about it. "Jon" wrote: All, I am creating a chart which is based on multiple columns worth of data. The problem is that the amount of rows changes and I have to be able to handle that programmaticaly. In other words, if the amount of rows was constant somewhere in my sub I would have something like: .Chart.SetSourceData Source:=Sheets("NameGoesHere").Range("A2:A5, F2:H5") 'where the Range A2:A5 contains names that goes to label the X-axis. 'And F2:H5 represents the data for each name in A2:A5. No problem. However, I need that range to vary. I was hoping something like this would work: .Chart.ChartWizard Source:=Worksheets(xlSheet(I).Name).Range(xlSheet( I).Cells(2, 1):xlSheet(I).Cells(z, 1), xlSheet(I).Cells(2, 6):xlSheet(I).Cells(z, 8)) 'where z is an integer which calculated the number of rows needed. Problem. 'Didn't work As you can see I tried to mimic the form used for static data but failed miserably. Any help would be greatly appreciated. Clear as mud? Thanks in advance. -- J |
#6
|
|||
|
|||
Jon -
You found something that works. This command in your earlier post must have been causing major problems: .Chart.Range("A2", "A" & .Range("A2", "A65534").SpecialCells _ (xlCellTypeBlanks).Cells(1).Row - 1).Name = "data" Since a Chart has no Range method or property, the line can only lead to run time errors. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Jon wrote: Markus, Thanks for the quick response. I see exactly what you are trying, but I cannot get my code to run with it. Here's the relevant snippet, tell me if this is what you had in mind. Set ch = Worksheets("Charts").ChartObjects.Add(Left:=15, Width:=700, _ Top:=(J * (225 + 25) + 50), Height:=225) With ch .Chart.SetSourceData Source:=Sheets(xlSheet(I).Name).Range(xlSheet(I). _ Cells(2, 6), xlSheet(I).Cells(z, 8)) .Chart.Range("A2", "A" & .Range("A2", "A65534").SpecialCells _ (xlCellTypeBlanks).Cells(1).Row - 1).Name = "data" End With I end up with a run-time error 438 "Object doesn't support this property or method" What am I missing here? "Markus Scheible" wrote: Hi Jon, I would try another procedu Link the chart with a named range, e.g. ..Chart.SetSourceData Source:=Sheets("NameGoesHere").Range ("arguments") Then you can name the arguments range, starting at a known cell e.g. A2 and down all filled rows with: ..Range("A2", "A" & .Range("A2", "A65534").SpecialCells (xlCellTypeBlanks).Cells(1).Row - 1).Name = "data" For info: xlCellTypeBlanks gives you the first empty cell, therefore you need to use .Row - 1 Best Markus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic charting problems (events) | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |