Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a worksheet as a variable in a Active Chart Series
I have about 100 sheets each named in terms of a node in a telecom network.
For each node I have to graph a series of values and also find a max within a range. I want to use a variable naming convention for worsheets in my VB code. For example in the following line of code instead of the sheet name I would like to use a variable such as str: dim str as string Sheets("CCA01(7011)").Activate str = ActiveSheet.Name ActiveChart.SeriesCollection(1).XValues = "='CCA01(7011)'!R2C4:R14C4". Instead writing this line for each sheet I want to use a loop to each sheet and for each sheet I want to execute the above code. Let us say my sheets are named MSC01, MSC02, MSC03,... Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a worksheet as a variable in a Active Chart Series
Hi Farooq,
this is only a stab in the dark, and I'm assuming you have less than 100 sheets. K = K + 1 ActiveChart.SeriesCollection(1).XValues = "='MSC" & IIF(K9,"0","") & "'!R2C4:R14C4" inside your loop. Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a worksheet as a variable in a Active Chart Series
Hi Farooq,
this is only a stab in the dark, and I'm assuming you have less than 100 sheets. K = K + 1 ActiveChart.SeriesCollection(1).XValues = "='MSC" & IIF(K9,"0","") & "'!R2C4:R14C4" inside your loop. Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a worksheet as a variable in a Active Chart Series
For Each sh In Activeworkbbok.Worksheets ActiveChart.SeriesCollection(1).XValues = "='" & sh.Name & "'!R2C4:R14C4" Next sh -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Farooq Sheri" wrote in message ... I have about 100 sheets each named in terms of a node in a telecom network. For each node I have to graph a series of values and also find a max within a range. I want to use a variable naming convention for worsheets in my VB code. For example in the following line of code instead of the sheet name I would like to use a variable such as str: dim str as string Sheets("CCA01(7011)").Activate str = ActiveSheet.Name ActiveChart.SeriesCollection(1).XValues = "='CCA01(7011)'!R2C4:R14C4". Instead writing this line for each sheet I want to use a loop to each sheet and for each sheet I want to execute the above code. Let us say my sheets are named MSC01, MSC02, MSC03,... Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a worksheet as a variable in a Active Chart Series
Thanks for the reply. First of my apologies for misleading you by naming my
sheets as MSC01, MSC02, MSC03,.. The names are all necessary starting with MSC, it could be STP01, MOIP1 etc. If I refer to sheets using index number then for each sheet I can find its name using ActiveSheet.Name and then use this string in the formula. The question is how to use a string within the formula. Also how can I find the total number of sheets in workbook? Thanks again "Ken Johnson" wrote: Hi Farooq, this is only a stab in the dark, and I'm assuming you have less than 100 sheets. K = K + 1 ActiveChart.SeriesCollection(1).XValues = "='MSC" & IIF(K9,"0","") & "'!R2C4:R14C4" inside your loop. Ken Johnson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a worksheet as a variable in a Active Chart Series
Thanks Bob and Ken for your help.
Farooq "Bob Phillips" wrote: For Each sh In Activeworkbbok.Worksheets ActiveChart.SeriesCollection(1).XValues = "='" & sh.Name & "'!R2C4:R14C4" Next sh -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Farooq Sheri" wrote in message ... I have about 100 sheets each named in terms of a node in a telecom network. For each node I have to graph a series of values and also find a max within a range. I want to use a variable naming convention for worsheets in my VB code. For example in the following line of code instead of the sheet name I would like to use a variable such as str: dim str as string Sheets("CCA01(7011)").Activate str = ActiveSheet.Name ActiveChart.SeriesCollection(1).XValues = "='CCA01(7011)'!R2C4:R14C4". Instead writing this line for each sheet I want to use a loop to each sheet and for each sheet I want to execute the above code. Let us say my sheets are named MSC01, MSC02, MSC03,... Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable series multi line chart? | Charts and Charting in Excel | |||
Naming the Series | Excel Discussion (Misc queries) | |||
Copying Chart with Chart Series from Worksheet Name | Excel Programming | |||
How to embed chart in active worksheet with VBA? | Excel Programming | |||
Q. Pie Chart problem. Want to add series, from another worksheet. | Excel Programming |