If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using a variable for worksheet name in SERIES
Chris -
As usual, Excel provides several ways to skin this cat, and not all are so efficient. I usually find it more reliable to edit the components of the series formula directly, rather than build a new formula. Something like this: With ActiveChart.SeriesCollection(3) .Values = worksheets("RangeData").Range("rngACWP") .XValues = worksheets("RangeData").Range("rngDate") End With Although I just noticed that this changed the name to the static address. Try this: With ActiveChart.SeriesCollection(3) .Values = "='" & worksheets("RangeData").Name & "'!" & "rngACWP" .XValues = "='" & worksheets("RangeData").Name & "'!" & "rngDate" End With If the name is a workbook level name, Excel changes the sheet name prefix to the workbook name. If it is a worksheet level name, then Excel leaves the sheet name intact. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ reynoldscm wrote: Hi, I am trying to reference a dynamic range in my code that modifies an existing chart SERIES statement. However, whenever I enter the dynamic range name (i.e. rngACWP) Excel inserts the workbook file name in addition. I have tried referencing the sheet name...which didn't work, and then tried to establish a variable that referenced the ActiveWorkbook name but don't think I did right. So, I basically have two questions: 1. Is there a way to utilize some sort of variable in the SERIES statement so that the user isn't limited to a filename that I create for them but dynamically pulls their filename to use in the formula? 2. If the above anwer is yes, how would I define the variable and utilize it in the SERIES statement? Here is the snippet of code I am working with: ActiveSheet.ChartObjects("CUM Chart").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Formula = _ "=SERIES('RangeData'!R1C4,'Automated CSPR Charts -down to 2 buttons.xls'!'rngDate','Automated CSPR Charts -down to 2 buttons.xls'!'rngACWP',3)" Thanks for the help. Chris -- reynoldscm ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message450438.html |
#2
|
|||
|
|||
Using a variable for worksheet name in SERIES
John, Thanks for the help. It worked wonderfully! I did encounter one problem however. When there is no data in one of the series of the chart (in my case I have #N/A when no data is available) the macro stops running. Is this just a simple error handling opportunity or would there be some other piece of code that would allow it to continue processing? Chris Jon Peltier wrote: *Chris - As usual, Excel provides several ways to skin this cat, and not all are so efficient. I usually find it more reliable to edit the components of the series formula directly, rather than build a new formula. Something like this: With ActiveChart.SeriesCollection(3) ..Values = worksheets("RangeData").Range("rngACWP") ..XValues = worksheets("RangeData").Range("rngDate") End With Although I just noticed that this changed the name to the static address. Try this: With ActiveChart.SeriesCollection(3) ..Values = "='" & worksheets("RangeData").Name & "'!" & "rngACWP" ..XValues = "='" & worksheets("RangeData").Name & "'!" & "rngDate" End With If the name is a workbook level name, Excel changes the sheet name prefix to the workbook name. If it is a worksheet level name, then Excel leaves the sheet name intact. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ reynoldscm wrote: Hi, I am trying to reference a dynamic range in my code that modifies an existing chart SERIES statement. However, whenever I enter the dynamic range name (i.e. rngACWP) Excel inserts the workbook file name in addition. I have tried referencing the sheet name...which didn't work, and then tried to establish a variable that referenced the ActiveWorkbook name but don't think I did right. So, I basically have two questions: 1. Is there a way to utilize some sort of variable in the SERIES statement so that the user isn't limited to a filename that I create for them but dynamically pulls their filename to use in the formula? 2. If the above anwer is yes, how would I define the variable and utilize it in the SERIES statement? Here is the snippet of code I am working with: ActiveSheet.ChartObjects("CUM Chart").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Formula = _ "=SERIES('RangeData'!R1C4,'Automated CSPR Charts -down to 2 buttons.xls'!'rngDate','Automated CSPR Charts -down to 2 buttons.xls'!'rngACWP',3)" Thanks for the help. Chris -- reynoldscm ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message450438.html * -- reynoldscm ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message450438.html |
#3
|
|||
|
|||
Using a variable for worksheet name in SERIES
Chris -
Yes, the NA() thing works very well, except when it doesn't. The NA() lets you skip over points in a series, but if a chart series has only blanks or only #N/A (or I guess a combination of the two), it isn't charted, and VBA isn't as smart about some things as the regular Excel UI. You have two choices. First, your macro can put a dummy piece of data in the data range, so the series is temporarily charted while you adjust the ranges. When you're done, put the #N/A back. Second, you could temporarily change the chart type of the series to an Area chart, which treats #N/A as a zero. Adjust the series, then change the chart type back. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ reynoldscm wrote: John, Thanks for the help. It worked wonderfully! I did encounter one problem however. When there is no data in one of the series of the chart (in my case I have #N/A when no data is available) the macro stops running. Is this just a simple error handling opportunity or would there be some other piece of code that would allow it to continue processing? Chris Jon Peltier wrote: *Chris - As usual, Excel provides several ways to skin this cat, and not all are so efficient. I usually find it more reliable to edit the components of the series formula directly, rather than build a new formula. Something like this: With ActiveChart.SeriesCollection(3) ..Values = worksheets("RangeData").Range("rngACWP") ..XValues = worksheets("RangeData").Range("rngDate") End With Although I just noticed that this changed the name to the static address. Try this: With ActiveChart.SeriesCollection(3) ..Values = "='" & worksheets("RangeData").Name & "'!" & "rngACWP" ..XValues = "='" & worksheets("RangeData").Name & "'!" & "rngDate" End With If the name is a workbook level name, Excel changes the sheet name prefix to the workbook name. If it is a worksheet level name, then Excel leaves the sheet name intact. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ reynoldscm wrote: Hi, I am trying to reference a dynamic range in my code that modifies an existing chart SERIES statement. However, whenever I enter the dynamic range name (i.e. rngACWP) Excel inserts the workbook file name in addition. I have tried referencing the sheet name...which didn't work, and then tried to establish a variable that referenced the ActiveWorkbook name but don't think I did right. So, I basically have two questions: 1. Is there a way to utilize some sort of variable in the SERIES statement so that the user isn't limited to a filename that I create for them but dynamically pulls their filename to use in the formula? 2. If the above anwer is yes, how would I define the variable and utilize it in the SERIES statement? Here is the snippet of code I am working with: ActiveSheet.ChartObjects("CUM Chart").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Formula = _ "=SERIES('RangeData'!R1C4,'Automated CSPR Charts -down to 2 buttons.xls'!'rngDate','Automated CSPR Charts -down to 2 buttons.xls'!'rngACWP',3)" Thanks for the help. Chris -- reynoldscm ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message450438.html * -- reynoldscm ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message450438.html |
Thread Tools | |
Display Modes | |
|
|