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 |
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 |