Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Source Data for the Chart
Hi All,
One Small Issue: I am drawing a chart and this is the source data for that. ActiveChart.SetSourceData Source:=Sheets("Analysis").Range("E1:E11,Y1:AJ11") _ , PlotBy:=xlRows Is there any way I can put a variable in place of 11 in the range. This variable will count the data in column E. Also, Column AJ is the extreme, I may not go upto that. Is there anyway I can make that as a variable also. This variable counts the data in Y1:AJ1 and goes only that many columns to the right of Y1 Let me know if you people can help with this Thanks, Anshuman |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Source Data for the Chart
The string used for the Range function does not need to be a fixed string. So
you could have two cells in the sheet where the chart is, let's say B6 and B7 that contains the last row and last column to consider in the chart. Note that these cells can contain a formula as well, like =COUNT(E1:E1000) for example. Then you could put the following lines in your code: sRangeToDraw = "E1:E" & Range("Analysis!B6") & ",Y1:" & Range("Analysis!B7") & Range("Analysis!B6") Range("Analysis!B6") ActiveChart.SetSourceData Source:=Sheets("Analysis").Range(sRangeToDraw) _ , PlotBy:=xlRows Stephane. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Source Data for the Chart
Hi Stephane,
I have counted the number of rows and put the value in E1000 and the number of column in AK1. Then I tried inserting the following code: sRangeToDraw = "E1:E" & Range("Analysis!E1000") & ",Y1:" & Range("Analysis!AK1") & Range("Analysis!E1000") Range ("Analysis!E1000") ActiveChart.SetSourceData Source:=Sheets("Analysis").Range(sRangeToDraw) _ , PlotBy:=xlRows It says compiling Error. Can you please tell me what's wrong here? thanks, Anshuman On Jul 23, 2:16 am, Stephane Quenson wrote: The string used for the Range function does not need to be a fixed string. So you could have two cells in the sheet where the chart is, let's say B6 and B7 that contains the last row and last column to consider in the chart. Note that these cells can contain a formula as well, like =COUNT(E1:E1000) for example. Then you could put the following lines in your code: sRangeToDraw = "E1:E" & Range("Analysis!B6") & ",Y1:" & Range("Analysis!B7") & Range("Analysis!B6") Range("Analysis!B6") ActiveChart.SetSourceData Source:=Sheets("Analysis").Range(sRangeToDraw) _ , PlotBy:=xlRows Stephane. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Source Data for the Chart
Hi Stepahne,
Got IT.... THANKS MAN On Jul 23, 8:43 am, anshu wrote: Hi Stephane, I have counted the number of rows and put the value in E1000 and the number of column in AK1. Then I tried inserting the following code: sRangeToDraw = "E1:E" & Range("Analysis!E1000") & ",Y1:" & Range("Analysis!AK1") & Range("Analysis!E1000") Range ("Analysis!E1000") ActiveChart.SetSourceData Source:=Sheets("Analysis").Range(sRangeToDraw) _ , PlotBy:=xlRows It says compiling Error. Can you please tell me what's wrong here? thanks, Anshuman On Jul 23, 2:16 am, Stephane Quenson wrote: The string used for the Range function does not need to be a fixed string. So you could have two cells in the sheet where the chart is, let's say B6 and B7 that contains the last row and last column to consider in the chart. Note that these cells can contain a formula as well, like =COUNT(E1:E1000) for example. Then you could put the following lines in your code: sRangeToDraw = "E1:E" & Range("Analysis!B6") & ",Y1:" & Range("Analysis!B7") & Range("Analysis!B6") Range("Analysis!B6") ActiveChart.SetSourceData Source:=Sheets("Analysis").Range(sRangeToDraw) _ , PlotBy:=xlRows Stephane. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Source Data for the Chart
CORRECTED -- One line containing Range("Analysis!B6") was mistakenly inserted
in the code. Correct code reposted below "Stephane Quenson" wrote: The string used for the Range function does not need to be a fixed string. So you could have two cells in the sheet where the chart is, let's say B6 and B7 that contains the last row and last column to consider in the chart. Note that these cells can contain a formula as well, like =COUNT(E1:E1000) for example. Then you could put the following lines in your code: sRangeToDraw = "E1:E" & Range("Analysis!B6") & ",Y1:" & _ Range("Analysis!B7") & Range("Analysis!B6") ActiveChart.SetSourceData Source:=Sheets("Analysis").Range(sRangeToDraw) _ , PlotBy:=xlRows Stephane. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pie chart & column chart w/same source data | Charts and Charting in Excel | |||
Use detailed data in one worksheet to create summary data as chart source | Charts and Charting in Excel | |||
How do you link chart source data when you copy the chart? | Charts and Charting in Excel | |||
Source data for a Chart | Excel Discussion (Misc queries) | |||
Pie chart source data | Charts and Charting in Excel |