Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart data range automatically
Hi,
This is my second posting to the forum. I hope someone out there ca help me! I am trying to set the data range for my chart automatically. Som macros change the data in my worksheet with the data, and as a resul my chart data range may need to cover more/less rows and/or columns. So, every time I run the macros for my data worksheet, I need anothe macro to re-define the data range for my graph. More info: 1. I have variables that reflect the column and row numbers for m range that I can use 2. The data range does not consist of consecutive cells (there is a ga of data columns that should not be in the graph) I have gone far enough but at the end, something deters my vba cod from using my range, even though the range is being recognised a such. With my code below, I define two separate ranges, and then a third on that consists of the union of the two. Excel recognises that rang because it selects it, but does not manage to use it for the graph. My code: Sub ChangeChartRange1() Dim r1 As Range, r2 As Range, ChartRange As Range Worksheets("ChartData").Activate Set r1 = Worksheets("ChartData").Range(Cells(15, 2), Cells(1626, 2)) Set r2 = Worksheets("ChartData").Range(Cells(15, 4), Cells(1626, 11)) Set ChartRange = Union(r1, r2) ChartRange.Select Sheets("Chart").Select ActiveChart.SetSourceDat Source:=Sheets("ChartData").Range(ChartRange), PlotBy:=xlColumns End Sub When running this code I get: "Run-time error '1004': Application-defined or object-defined error" The following actually works: Sub ChangeChartRange2() Dim StringRange As String StringRange = "B15:B1626,D15:K1626" Sheets("Chart").Select ActiveChart.SetSourceDat Source:=Sheets("ChartData").Range(StringRange), PlotBy:=xlColumns End Sub The above works, but is not good enough for me because it requires tha the data range is a predefined string incorporated in the code, whil in my case I want it to be dynamically assigned based on some variable obtained from other code. The fact that the second code works means that the only problem ther is with the first version of code is using the range for the graph. I have tried to be as descriptive as possible. Does anyone know a way around this? Many thanks LoucaGree -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart data range automatically
Sub ChangeChartRange1()
Dim r1 As Range, r2 As Range, ChartRange As Range Worksheets("ChartData").Activate Set r1 = Worksheets("ChartData").Range(Cells(15, 2), Cells(1626, 2)) Set r2 = Worksheets("ChartData").Range(Cells(15, 4), Cells(1626, 11)) Set ChartRange = Union(r1, r2) ChartRange.Select Sheets("Chart").Select ActiveChart.SetSourceData Source:=Sheets("ChartData").Range(ChartRange.Addre ss), PlotBy:=xlColumns End Sub ======= or ========= Sub ChangeChartRange1() Dim r1 As Range, r2 As Range, ChartRange As Range Worksheets("ChartData").Activate Set r1 = Worksheets("ChartData").Range(Cells(15, 2), Cells(1626, 2)) Set r2 = Worksheets("ChartData").Range(Cells(15, 4), Cells(1626, 11)) Set ChartRange = Union(r1, r2) ChartRange.Select Sheets("Chart").Select ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns End Sub -- Regards, Tom Ogilvy "LoucaGreen " wrote in message ... Hi, This is my second posting to the forum. I hope someone out there can help me! I am trying to set the data range for my chart automatically. Some macros change the data in my worksheet with the data, and as a result my chart data range may need to cover more/less rows and/or columns. So, every time I run the macros for my data worksheet, I need another macro to re-define the data range for my graph. More info: 1. I have variables that reflect the column and row numbers for my range that I can use 2. The data range does not consist of consecutive cells (there is a gap of data columns that should not be in the graph) I have gone far enough but at the end, something deters my vba code from using my range, even though the range is being recognised as such. With my code below, I define two separate ranges, and then a third one that consists of the union of the two. Excel recognises that range because it selects it, but does not manage to use it for the graph. My code: Sub ChangeChartRange1() Dim r1 As Range, r2 As Range, ChartRange As Range Worksheets("ChartData").Activate Set r1 = Worksheets("ChartData").Range(Cells(15, 2), Cells(1626, 2)) Set r2 = Worksheets("ChartData").Range(Cells(15, 4), Cells(1626, 11)) Set ChartRange = Union(r1, r2) ChartRange.Select Sheets("Chart").Select ActiveChart.SetSourceData Source:=Sheets("ChartData").Range(ChartRange), PlotBy:=xlColumns End Sub When running this code I get: "Run-time error '1004': Application-defined or object-defined error" The following actually works: Sub ChangeChartRange2() Dim StringRange As String StringRange = "B15:B1626,D15:K1626" Sheets("Chart").Select ActiveChart.SetSourceData Source:=Sheets("ChartData").Range(StringRange), PlotBy:=xlColumns End Sub The above works, but is not good enough for me because it requires that the data range is a predefined string incorporated in the code, while in my case I want it to be dynamically assigned based on some variables obtained from other code. The fact that the second code works means that the only problem there is with the first version of code is using the range for the graph. I have tried to be as descriptive as possible. Does anyone know a way around this? Many thanks LoucaGreen --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart data range automatically
Hi,
Thanks for your reply. Now I get another error "Run-time error '9' Subscript out of range" Nevermind though. The solution was given to me at the Charting forum. I had to change th Source:=Sheets("ChartData").Range(ChartRange.Addre ss) to Source:=ChartRange.Address. Thanks LoucaGree -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart data range automatically
No, you needed to change
Source:=Sheets("ChartData").Range(ChartRange), to Source:=ChartRange.Address Source:=Sheets("ChartData").Range(ChartRange.Addre ss) would work as well. I also gave you that correction. Subscript out of range would indicate that you don't have the proper name for your sheet. -- Regards, Tom Ogilvy "LoucaGreen " wrote in message ... Hi, Thanks for your reply. Now I get another error "Run-time error '9' Subscript out of range" Nevermind though. The solution was given to me at the Charting forum. I had to change the Source:=Sheets("ChartData").Range(ChartRange.Addre ss) to Source:=ChartRange.Address. Thanks LoucaGreen --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart data range automatically
Tom -
I think you meant to Source:=ChartRange - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tom Ogilvy wrote: No, you needed to change Source:=Sheets("ChartData").Range(ChartRange), to Source:=ChartRange.Address Source:=Sheets("ChartData").Range(ChartRange.Addre ss) would work as well. I also gave you that correction. Subscript out of range would indicate that you don't have the proper name for your sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically shift chart data range | Excel Discussion (Misc queries) | |||
setting scale on chart automatically | Excel Discussion (Misc queries) | |||
setting a range automatically | Excel Discussion (Misc queries) | |||
How to have a chart automatically adjust range as I add data entri | Excel Discussion (Misc queries) | |||
How to have a chart automatically adjust range as I add data entr. | Charts and Charting in Excel |