Then I want to do the same for the legends, i.e. "I current") so the chart legend does not get to crowded.
EXCEL TRENDLINE FOR MULTIPLE SERIES SERIES
Legend entries for all but the first series (i.e. I plot all the series with the same color and remove the I have the data column I current with over 96000 rows. I want to delete the legend entries for some of the trendlines belonging to the series, while keeping the trendlines themselves on the chart.Į.g.
I have a single trendline for each data series, and multiple data series. Thank you for your fast reply, but I am not sure if I get your point. If Not InStr(1, sel.Name, active.Name, vbTextCompare) = 0 Then Exit For End If 'Else take the next i Next i Set active = ActiveChart.SeriesCollection(i) Function findIndexOfSelectedSeries() As Integer Dim i As Integer Dim sel As Seriesįor i = 1 To ActiveChart.SeriesCollection. and the "I electrolyser 32000-64000 'series is selected then the function returns the series index 'of the series with the name "I electrolyser", since the trendline 'plotting needs to start from there. 'So if the series are named " I electrolyser, 'I electrolyser 32000-64000. the first series in the data column 'from which the selected series was drawn). 'findIndexOfselectedSeries: 'goes through the seriesCollection of the activeChart until 'it finds the series, then it returns the index to the first 'part of the series (i.e. save only the first one of all the created If Not i = startSeriesIndex ThenĪ(legendEntriesAmount + 2).Delete 'make it moving average with given period and color With ActiveChart.SeriesCollection(i).Trendlines(1)Įnd With 'delete extra legend entries, i.e. 'for all series in that data column For i = startSeriesIndex To (startSeriesIndex + seriesPerDataColumn - 1) Step 1ĪctiveChart.SeriesCollection(i).Trendlines.Add
EXCEL TRENDLINE FOR MULTIPLE SERIES CODE
Count Dim i As Integer 'Call the code for turning UI updates on Call restoreUIUpdating
'find out how many legend entries there are before we insert the trendlines Dim legendEntriesAmount As Integer SeriesPerDataColumn = Floor(rowcount / 32000, 1) + 1 Rowcount = countDataRows( "Logging data") 'count the rows in the logging data sheet 'check if the user pressed cancel If userColorIndex = 0 Then Exit Sub End If 'Call the code for turning UI updates off Call disableUIUpdating "7 = brown, 8 = black, 9 = olive green, 10 = dark aqua", Type:=1) "(give the answer as a number between 1-56)" _ "What color should the trendline be?" & vbNewLine & _ UserColorIndex = Application.InputBox(prompt:= _ if the user enters any other 'number than 7-10) 'check if the user pressed cancel If movingAveragePeriod = 0 Then Exit Sub End If 'ask the user to choose a color '(beside the colors defined in the defineColors sub this uses the default 'excel colors (see resetToDefaultColors), i.e. "(Give a value between 2 and 250)", Type:=1) "How long should the period of the moving average be?" _ MovingAveragePeriod = Application.InputBox(prompt:= _ 'ask user about how long moving average should be used 'Call the code for turning UI updates on Call restoreUIUpdating StartSeriesIndex = findIndexOfSelectedSeries 'Define variables Dim seriesPerDataColumn As Integer Dim movingAveragePeriod As Integer Dim userColorIndex As Integer Dim startSeriesIndex As Integer Dim rowcount As Long 'Find the index of the first series in the same data column as the selected one 'define the used colors Call defineColors 'Call the code for turning UI updates off Call disableUIUpdating 'This sub adds a moving average trendline to the selected data series Sub addMovingAverageTrendline() My problem is that my macro stops working if I run it for a series and then remove the trendlines manually for these series and run the macro again.Īccording to my debugging the code fails since excel does not always add a legend for the trendline when I add the trendline with this command: Thus the chart (especially the legends) should look like this: This macro also removes the all trendline legends except for the legend of the first one.
Thus I have constructed a macro that adds moving average trendlines to the series when one of the series in this data column is selectedĪnd the macro is run. Then I want trendlines for each series in a single data column that in the same way as the series only have a single legend. as a result I have as many legends as there are data columns. After plotting the multiple series I remove all legends except for the first one Each data column is plotted with multiple series since there are more data than one series can contain (>32000 rows). I have charts containing data from multiple data columns.
0 kommentar(er)
