Assign Named Range To Chart Series Collection

Feb 9, 2008

I am trying to assign a named range to a series on a preexisting chart in a sheet, but I get the error that "unable to set values property of series class". This is the code example that DOESN'T work:

Sub assign_values()
Dim myrange As range
Set myrange = range("a1")
Set myrange = union(range("a5"),myrange)
activechart.seriescollection(1).values = myrange
End Sub

However, this code DOES work:

Sub assign_values()
activechart.seriescollection(1).values = range("a1")
End Sub

I don't see why the first wouldn't work, but the second would work. I suspect it is something to do with the union function. i am trying to go through a set of data and then group certain cells into a named range to be used on the graph.

View 3 Replies


ADVERTISEMENT

Name Each Name Series Collection Of Chart

Feb 7, 2008

I hope this passes muster. Anyways, I wrote some VBA code to handle an embedded chart in my workbook. I named the chart "Data" but i'm getting some unexpected trouble from VBA. Last time I worked on this set of code, it ran fine. When I try to run it now, it is throwing up an error: "1004 Unable to set name property of series class" The .name function is not working correctly but everything up till that point is working correctly. I have 5 sets of data in this chart also ....

View 9 Replies View Related

Reference Series Collection In Chart

Nov 30, 2007

I'm looking for a way to name lines in a pivot chart instead of default numbering eg ActiveChart.SeriesCollection(1). This in order to attach variable lines to y-axis no 2 no matter which selection is made in the pivot.

View 3 Replies View Related

Dynamic Named Range In Chart Data Series

Nov 30, 2006

I am attempting to use a dynamic named range in a chart data series as described in Excel Hacks (Hack #42 and Hack #52).

Using Insert|Name|Define I have created a named range called CashFlowSaleChartDataRange that is set to the following value:

=OFFSET('Cash Flow-Sale'!$O$10,0,0,NumFlows,1)

When I subsequently assign a reference to this named range in the data series dialog it will accept the answer and my chart will adjust to reflect the updated range. However, if I return to the data series dialog, I now see the range address returned by the OFFSET function rather than the named range reference.

View 2 Replies View Related

Conditional Series Collection Colour For Chart

Sep 24, 2009

I am using a bar graph and would like conditional formatting for a series collection. So if the series collection is greater than 30%, the single entry (not the whole series) would turn red. I have attached an image of what I currently have. There is a data validation list at the top which tells the graph what series to use, so the data is always changing.

I hope this all makes sense. Basically, if an entry is above 30%, I want the single entry in the series to turn red.

View 2 Replies View Related

Coloring Pie Chart Using Series Collection Only Returns One Color?

Jul 22, 2014

I am trying to change the color of piechart using the code below:

Code:
Sub ColorPieChart()
Dim i As Integer
Dim myCol(10) As Long

[Code].....

Basically, I want each series in my graph to have the colors formulated by myCol. The code above, however, only colors the graph with color given my myCol(1), so it seems as if something is wrong with my loop.

View 8 Replies View Related

Change Order For Chart Series Collection Macro Code

Mar 18, 2008

I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?

With cht
.ChartType = xlColumnClustered

' first data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With

' second data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With

View 2 Replies View Related

Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference

Nov 11, 2012

I am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter

=--(AND($AX8>$AA$4,$Y8>0))

and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

View 1 Replies View Related

Create Chart Using Dynamic Named Ranges For Series

Jul 27, 2012

I've created my dynamic named ranges using the OFFSET function, ex.

="OFFSET(SAMPLE!$D$4,1,0,COUNTA(SAMPLE!$D:$D)-1,1)"

I'm now trying to get my chart to use that range. I read at [URL] .... that I need to make sure my series reference is a fully qualified reference. So I've entered that series reference using the name of the workbook followed by the range name (=2012-PIRS.xlsx!SAM_CLAR2DEL). I have two copies of my workbook (one as xlsm with VBA project, and one with xlsx without VBA) and I can't get Excel to accept the series value in either workbook.

ERROR MSG:
The formula you typed contains an error. Try one of the following:
- Make sure you've included all parentheses and required arguments.
- To use a function, click Insert Function on the Formulas tab (in the Function Library group).
- If you include a reference to another sheet or workbook, verify that the reference is correct.
- If you are not trying to enter a formula, avoid.........

View 1 Replies View Related

Change Chart Data Series (rows) Using Named Ranges?

Mar 13, 2013

I have a chart in a workbook that works fine by looking at the following data series:

=Workings!$A$2:$C$35

In the data series above...where the numbers are 2 and 35 I have named ranges "CHART_Start_Row" and "CHART_End_Row" which will change dynamically as appropriate and can replace these static numbers - but how do I incorporate these named ranges into the data series reference?

View 2 Replies View Related

How To Assign List To Named Range

Oct 11, 2012

I have the following VBA code which created a unique list of values in a data column. After list is created, I want to assign it to a named range ReferTo parameter. Currently I am getting compiler error. How can I assign this unique list to a named range?

VB:
Dim UList As New Collection
Dim rCell As Range
....
If TypeName(Selection) = "Range" Then

[Code].....

View 4 Replies View Related

Assign Named Range To A Vba Range Variable?

Jan 25, 2013

I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")

I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.

the first two lines of code work fine, the msgbox shows "E100:E105", happy days!

However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.

VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup

MsgBox Range(thisString).Address

Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)

View 1 Replies View Related

Named Range Graphs With Many Series

Jun 4, 2007

I understand that you can do dynamic named ranges for graphs, using one named range for each series and another for the categories. However, the graph I'm working on will have a lot of series'. Is there a way to do this cleverly? I'm trying to avoid making a named range for each one.

View 2 Replies View Related

Series Collection Vba

Jan 6, 2010

I trying to code a SeriesCollection in vba, below is the code I have:

' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).Name = "='Chart1'!$C$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!$D$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!$E$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!$F$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!$G$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!$H$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!$I$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!$J$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!$K$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!$L$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!$M$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!$N$1"

However it it giving the error "Object denfined or Appication denfined"

View 9 Replies View Related

Add Series Collection From Dynamic Sheets

Jul 29, 2014

I've created one file: Mappe1_results.xlsm

This file ask in another file "Mappe1_ground.xlsx" for "B" and "N". Now one new sheet is created with the name "month-2014" and shows me which Category (B) is how often referred in one month. After that one chart is created for a better representation. This can be done for every month (which month is selected by the user over the button "Auswertung" in Sheet "Tabelle1").

Now I have a few problems / requirements:

1. If one user is using (maybe) Jan as his selected month, in the sheet "Auswertung" should be one duplicated chart of the Jan Chart., with no other (previous) series. Because Jan has no previous conditions (prev. year).
1.1 If one user is using another month (maybe Feb), in the sheet "Auswertung" should be one chart with both series of Jan and Feb and so one (for the other months). So that in "Auswertung" the chart is one comparison over the months. Only Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct/Nov/Dec have one prev. month.

For example: If one user write "Apr" into the inputbox and "Mar" is in the sheet existing, so in the chart of "Auswertung" should be April and March shown.

Some functions are set in my macros, but the problem is that the results are not equal between the sheet "Auswertung" and maybe "Jan-2014" or "Feb-2014". Because in "Auswertung" we need called all Categories (they can be found in "Referenz" - A).

All what I want is in "Auswertung" one chart with all present categories of "Referenz" - A and the series of the created sheets by the user.

2. The next problem is, every created sheet has one legend "Anzahl im ..." - this legend of every series should be shown in "Auswertung", too. So that we know which color is for which month, u know.

View 2 Replies View Related

Entering X And Y Values To Series Collection?

May 19, 2013

I have a VBA module in Access that extracts data and copies to an Excel spreadsheet. Every time I run the code additional data is extracted and displayed on a chart together with previous data. When I enter values to the series collection the first axis be it X or Y is entered correctly but the second axis produces error 1004 'unable to set XValues property of the series class' If I swap the lines of code around the error switches to 'Values' (y axis)

wchart.SeriesCollection(NoCurves).Values = wsheet.Cells(9, 3 + (3 * (NoCurves - 1)))
wchart.SeriesCollection(NoCurves).XValues = wsheet.Cells(8, 3 + (3 * (NoCurves - 1)))wsheet.Cells(a, b))
wchart.SeriesCollection(NoCurves).Name = wsheet.Range(Cells(6, 2 + (3 * (NoCurves - 1))), Cells(7, 2 + (3 * (NoCurves - 1))))

View 2 Replies View Related

Series Collection Names Instead Of Numbers?

Aug 30, 2013

I have a graph with something like 40 series that are being plotted. For one, I create non-standard data labels using VBA. In that code, I refer to ChartObjects(1).Chart.SeriesCollection(34). At some point I deleted a series that preceded it in the "select data" list and the code crashed. Took a lot of digging to learn that SeriesCollection(34) had become SeriesCollection(33).

To avoid this in the future, is there a way to refer to the SeriesCollection by a unique name rather than a sequential number?

View 4 Replies View Related

How To Get Index (number) Of Series Collection Selected In Activechart

Jul 25, 2012

How to get the index number of a seriescollection within the activechart user has selected .

I am able access the chart selected by the user by using the activechart function, but next i am trying to access the seriescollection selected by the user, in other words the activeseriescollection thing.

Is there any way to access the active seriescollection of an activechart or any other way to access the seriescollecion of a chart selected by a user.
I googled a lot but what i found that we have give the index no. i.e; activechart.seriescollection(1)......

View 2 Replies View Related

Excel 2010 :: Dynamic Changing Of Series Collection Values?

Jul 6, 2014

I still get the 'Object doesn't support this property or method. Error code 438' error. I am using Excel 2010

VB:
j = 3
Do Until j = 6
ActiveWorkbook.Sheets(k).Activate
Set chartX = ActiveSheet.ChartObjects("X")

[Code]....

The macro is supposed to dynamicaly adjust series range for 3 charts for x,y,z, values in each of sheets. Charts are a line type.

View 3 Replies View Related

How To Access Excel Charts Series Collection Linked To Empty

Apr 23, 2003

I'm trying to use VBA to automatically change the data displayed in an excel chart. To do this I count the number of items displayed on the chart using Chart.Seriescollection.Count. I then loop over the Chart.Seriescollection(x) and try to change the Chart.Seriescollection(x).Formula string to link to the cells I want.

Problem is that, sometimes, the cells the curve is currently linked to are empty. Such a curve does show up in the Count, but I can't access the .Formula, even though this can be done manually from within Excel.

View 9 Replies View Related

Chart Series Does Not Accept Dynamic Range

Nov 26, 2006

Using the Dynamic Named Range tip, I defined named variables as follows:

Xrange=OFFSET($A$2,0,0,COUNT($A:$A),1)
Yrange=OFFSET($B$2,0,0,COUNT($B:$B),1)

then tried to define my Chart series as follows:

=SERIES($B$1,Xrange,Yrange,1)

but it gives an invalid reference error. What am I doing wrong?

View 2 Replies View Related

Use Range Address In Cell For Chart Series

Sep 15, 2007

I am trying to create an XY scatter plot with multiple series. Normally, one has to manually type in or mouse-drag a range for each x series and y series. Instead I would like to build these two ranges (x and y) with text functions and place them in a cell.

For example:

A1: 'data'!$C$3:$C$10
B1: 'data'!$D$3:$D$10

Then, when I create a chart, instead of mouse-dragging, for the x-series, I'll indicate the range that's specified within Cell A1, and for the y series, that which is in B1. I would then repeat this for each series. The reason I want to do this is because I have dozens of series and dozens of graphs and it is relatively easy to programatically identify the range boundaries, but long and tedious to mouse drag them.

View 3 Replies View Related

Variable Range For Chart Series Data

May 23, 2008

I have data which is imported from a txt file and always starts at the same row (R19) but is variable in length (end of data R??). I want to be able to select the chart xvalues (R19C10:R?C10) and values (R19C4:R?C4) based on the number of cells with data in them for a given column. In addition I believe I'm having troubles with sheet references, when the txt file is opened the sheet name is set to the file name, since I want to use this macro on many different files I need the sheet referencing code to account for this. Anyway here is the section of code which seems to be giving me trouble (the initial sections just create a new column of data which needs to be referenced as the xvalues) ...

View 3 Replies View Related

VBA - Retrieve Chart Series Source Data In Range?

Jan 13, 2010

Currently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?

This is what I've written so far:

Code:
Sub ammend_datarange_series_all_charts()
Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer

[Code]....

As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.

View 4 Replies View Related

Plot Each Selected Row Range As Series In Scatter Chart

May 16, 2008

I need to plot multiple series in a scatterplot. The problem is that the number of series is user-defined. And also, the number of data poitns in each series is different. I've looked it up already, but a lot of the examples i've seen don't account for the fact that I need to run through a loop and add series into my graph.
In other words, i need a way to select a certain range each time it runs through the loop, and add the data as a series into the chart. Also, the user enters how many series there are, so I use that as a counter

View 9 Replies View Related

VBA - Are Named Ranges Part Of Collection?

May 27, 2014

Writing up some code, and I have a workbook with many named ranges. Question is simple...

Are named ranges a part of a collection object that I can use?

Looking to do something like:

[Code] .....

How to determine collection objects on my own in the future!

View 2 Replies View Related

Add Series To Middle Of Data Range Without Disrupting Chart Format

Mar 7, 2014

I have a set of data listed by country, I need to add a new country to the list and I want to keep everything in alphabetical order. My problem is that adding a new series to the range anywhere but the end throws of the series formatting for my chart. The plotted points on my chart are the countries flag, so when everything shifts down a row the flags no longer represent the proper country. Is there an easy way to add the new country and keep my formatting lined up or will I need to redo the formatting for all the alphabetically lower countries?

View 5 Replies View Related

Scatter Plot Chart With Multiple Series From Selected Range

Jan 31, 2009

What I want to achieve is a script that would create a new scatter plot of multiple series from a selected block of cell. I found a thread that was similar but what is different from my data is that my x values are different for each series.

The format of the data is in the following format, the first series will the first x,y pair, the 2nd series would be the second x,y pair. I don't know if it'll be an issue but one thing is that the date might be different lengths. For instance, the 3rd series, only has 4 data points, whereas the other series contains 6 data points?

x0.200.400.600.801.001.20
y5.002.501.671.251.000.83
x0.100.200.300.500.701.30
y5.002.501.671.000.710.38
x0.100.300.400.50
y6.672.221.671.33

So essentially, is there a way I can have the script say, select your range, and then graph the selected range as a scatter plot?

View 9 Replies View Related

Chart Will Not Accept Named Range

Oct 12, 2012

I am trying to set up a chart, such that its charted ranges can be change by changing some cell values.

I am having a hard time getting past first base, however.

I have created a named range:

Code:
Name: LowerTrend
Refers To: = Offset(Values!$AV$200,0,0,800,1)

When I try to edit the chart series by placing 'LowerTrend' into Series values it will select the range by showing the dashed line around it, but it will not chart the data and when I exit the 'Edit Series' dialogue and then come back, the named range is not shown.

View 3 Replies View Related

Using A Worksheet's Named Range In A Chart

Apr 21, 2006

I have a chart with a series whose formula looks like this:

=SERIES("ACTUALS",BOD!$E$42:$AB$42,'OE2_EOM_Mar_06_(20APR_0900)_EDIT_THIS_ONE.xls'!BodOblActData,7)

The named range, BodOblActData, is a named range in the workbook, but how can I change the formula to use a named range in the work sheet?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved