Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell addresses selection for chart source
I am trying to create a macro to select a range of cells from the same
row, but the columns may or may not be contiguous and the cell references are not constant (they will change every month). Then use this selection for the SetSourceData in a chart. For example if I manually select the following using the CNTL key: a1,a2,a3,a4,a5, a7,a8,a9,a10,a11 (as in a 2 weeks worth of data) I would use this selection in the source for a chart. Should I start the cell selection from Starting Point A and go through a loop to select the necessary cells and place into an array? Or should I use a set range command? I hope I am being clear. Any help would be appreciated. Thank you. Leslie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell addresses selection for chart source
set rng = Range("A1:A5,A7:A11")
-- Regards, Tom Ogilvy wrote in message ps.com... I am trying to create a macro to select a range of cells from the same row, but the columns may or may not be contiguous and the cell references are not constant (they will change every month). Then use this selection for the SetSourceData in a chart. For example if I manually select the following using the CNTL key: a1,a2,a3,a4,a5, a7,a8,a9,a10,a11 (as in a 2 weeks worth of data) I would use this selection in the source for a chart. Should I start the cell selection from Starting Point A and go through a loop to select the necessary cells and place into an array? Or should I use a set range command? I hope I am being clear. Any help would be appreciated. Thank you. Leslie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell addresses selection for chart source
Leslie -
If you need to incorporate some kind of test to determine which cells to include, start with the entire range, and loop through to add the appropriate cells: Dim rngCell as Range Dim rngWholeRange as Range Dim rngToChart as Range Set rngWholeRange = Range("A1:A11") For Each rngCell in rngWholeRange.Cells If {rngCell passes the test} Then If rngToChart Is Nothing Then Set rngToChart = rngCell Else Set rngToChart = Union(rngToChart, rngCell) End If End If Next ' rngCell - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ wrote: I am trying to create a macro to select a range of cells from the same row, but the columns may or may not be contiguous and the cell references are not constant (they will change every month). Then use this selection for the SetSourceData in a chart. For example if I manually select the following using the CNTL key: a1,a2,a3,a4,a5, a7,a8,a9,a10,a11 (as in a 2 weeks worth of data) I would use this selection in the source for a chart. Should I start the cell selection from Starting Point A and go through a loop to select the necessary cells and place into an array? Or should I use a set range command? I hope I am being clear. Any help would be appreciated. Thank you. Leslie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell addresses selection for chart source
Jon, the code has worked great! Thank you for saving me hours of
frustration. I was able to put in TESTING criteria and refer to ranges using variables instead of the constants which I used in my example. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell addresses selection for chart source
Glad to help.
- Jon looloo wrote: Jon, the code has worked great! Thank you for saving me hours of frustration. I was able to put in TESTING criteria and refer to ranges using variables instead of the constants which I used in my example. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell addresses selection for chart source
Jon,
May I ask for a bit more detail from your response to the above? Your recommendation excluded (I believe) the object reference. Amateur logic told me to add these lines after Dim statements, but of course they're incorrect. Would you mind helping me to correct my error please? Worksheets("Chart").ChartObjects("Chart4").Activat e ActiveSheet.SetrngWholeRange = Range("B163:R171") Also, my "test" using the Len property and "or" statement may also be incorrect, but the code hasn't passed to that point yet. If {Len(rngCell.Value) = 0 Or rngCell.Value = 0}Then Essentially hoping for the entire subroutine. Any help is much appreciated. "Jon Peltier" wrote: Glad to help. - Jon looloo wrote: Jon, the code has worked great! Thank you for saving me hours of frustration. I was able to put in TESTING criteria and refer to ranges using variables instead of the constants which I used in my example. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
Limiting selection in a cell AND linking that selection to a list | Excel Discussion (Misc queries) | |||
link a chart source to a cell reference | Charts and Charting in Excel | |||
Copy text hyperlinks and paste them as source addresses? | Excel Discussion (Misc queries) | |||
Hiding source cell data after creating chart | Charts and Charting in Excel |