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)......
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 ....
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.
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)
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?
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.
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.
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.
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.
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.
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
I have created a userform that upon clicking a commandbutton adds a line of 4 textboxes. Everytime the user clicks the commandbutton a new line of textboxes is created. The 4 textboxes in each line are described by a class (hope I am using the terminology correctly). And each instance is saved in a collection. The problem is that I am giving the user the ability to insert a new row in between two existing rows.
When this happens, I need all of the index numbers to re-order so that they are consecutive from the top of the form to the bottom. Example: The user enters 6 rows of data and then realizes that they missed an item that needs to be inserted between rows 3 and 4. Currently the rows are indexed 1 to 6 with 1 being the row at the very top of the form and 6 being the row at the very bottom of the form. If I insert a row in between 3 and 4 I will end up with the index numbers going from top of the form to bottom (1,2,3,7,4,5,6). Is it possible to reorder the index numbers so that the inserted rows index number will be 4 and each row after that will go up by one?
I have several collections of objects in a macro I have created. However I would now like to be able to shift the order of these objects around (ie increase or decrease its index by 1 each time, and shift the other objects around it). For example I have collection1 which contains three objects x,y, and z with indexes 1, 2, and 3 respectively. I now want to give y the index of 1, and so shift x up to 2 so the resultant order is y, x, z. Is there anyway I can do this whithout copying all objects to another collection and recopying back in the desired order?
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
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
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?
Is there any way to use an INDEX and MATCH formula based on the row that is currently selected? If not, is there any other function that can be used to accomplish the same?
I need the formula to use whatever is in column A of the current row selected as the criteria used to match the information to be returned.
I am trying to export a chart using the code below, the loops change the data that is used to produce the chart:
Dim strFilename As String Dim strExt As String Dim lngProfile As Long Dim lngOption As Long For lngOption = 1 To 3 Range("nmOption") = Choose(lngOption, "Existing", "Option 4", "Option 5") For lngProfile = 1 To 4 Range("nmProfile") = "Profile " & lngProfile ActiveSheet.ChartObjects("Chart 13").Activate ActiveChart.ChartArea.Select strExt = "gif" strFilename = ThisWorkbook.Path & "Images" & _ Range("nmOption") & "_" & Range("nmProfile") & "." & strExt ActiveChart.Export strFilename, strExt Next lngProfile Next lngOption
Each export command creates a file with the correct name but no data (0kb in size) and a temporary file that is the correct file type (I have tried both jpg and gif).
I could try and copy the temporary file, but why does Excel not finish the job? I have tried to put a loop in and also added DoEvents after the export but no joy.
Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.
The Make-Models tab has the lists used to create the drop-down selections.
The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.
Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.
2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.
Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.
I created a formula in column A "=MID(E2,20,4)". I drag this down the column and I get the following results
1021 42_u
The first result is fine but the problem is the second result. Because I am looking at fixed length I am often getting something other than the file number which I seek.
Is there a way to do a formula that will go to my starting positions (this is a constant starting point) and then look for the numbers before the first "_" as a stopping point? The maximum will always be 4 digit numbers but the problem is there are 1-3 digit numbers as well.
I have different series number from column B to Q in Data Sheet, what I want is see strat and end range in temp sheet and delete(Shift xlup) numbers from Data Sheet from col a to q. If Series is like .....
I have a userform which saves its info to a spreadsheet. what i want to do is to also open a new workbook, save the same info to the new workbook, copy and paste from existing spreadsheet will do instead of linking the userform to a different workbook (i can do all this) I then want to save the workbook in a specific folder(folderA) but named by the next number ie workbooks 1, 2, 3, 4, 5, 6, 7, 8 etc exist so the next workbook should be called 9 so I need some code to look for the names of existing workbooks in the folder and determine the next. Then I want a spreadsheet to at a pre-set interval(15 minutes) open each workbook , copy the line to the next blank row and move the workbook to a second folder(folderB). The folders A & B will only contain these workbooks.
I would like to use an (I think) easy SUMIFS formula. In this case I'm using only 3 criteria, and 2 of them work perfectly. With the third I want to SUM only IF the series begins with 112 in the example.
