VBA generate yearly line graph for data

Question

I need help with my function which I want to generate a chart from two columns of data. The first column contains dates, about one every month, and the second is data for those dates (gas usage). I want the chart to be a line graph which has separate line series for each year (I got that working okay). I want the points to be monthly, since the data is monthly, and running from January to December.

The problems I'm having now are that the bottom axis runs from whatever the first month listed is (November in the sample picture) and contains more than 12 months since some months are listed more than once, since readings were taken more than once that month. Eventually I want these to be summed for one value in a month, but I'll figure that later. Also, the values don't seem to correspond with the months in the column beside them, but just go in the order listed.

Here is a link to a sample picture of the sheet and the current chart

And here's my current code:

        'These dates will be compared to see if the year has changed
        NextDate = ActiveCell.Value
        LastDate = ActiveCell.Value

        j = 5
        K = 1

        'Makes the linechart
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlLineMarkers

        'For some reason a bunch of series are made automatically
        'Delete all those garbage series...
        For Each s In ActiveChart.SeriesCollection
            s.Delete
        Next s

        'I have a function that already found the last row and assigned it to LastRow... So this runs through all of column A, checks if the years are different and makes a series when the years are different
        For i = 5 To (LastRow)

            NextDate = ActiveCell.Offset(i - 5, 0).Value

            If (Year(LastDate) <> Year(NextDate)) Or (i = LastRow) Then
                'The series starts at value after the end of the last 
                'series, or at row 5
                Set rngRange = ActiveSheet.Range(Cells(j, 1), Cells(i, 1))

                'K counts up for every series made, starting at 1
                ActiveChart.SeriesCollection.NewSeries
                ActiveChart.SeriesCollection(K).Name = "=""" & Year(LastDate) & """"
                ActiveChart.SeriesCollection(K).Values = rngRange.Offset(0, 4)
                'Right now I have a function which copies the months into column H. 
                'Later I'll get it to just use the dates and change the axis to months I think
                ActiveChart.SeriesCollection(K).XValues = rngRange.Offset(0, 7)

                'Stop
                j = i
                K = K + 1
                LastDate = NextDate
            End If

        Next i
        ActiveChart.ChartStyle = 34
        ActiveChart.ApplyLayout (1)
        ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
        ActiveChart.ClearToMatchStyle

Thank you for your help, and I hope my code makes sense. I'm learning as I go.

values for series changing which is ok but XValues latabels should be constant for all series. Have you tried to make it fixed like ...XValues = range("h5:h16")? — Kazimierz Jawor
I have tried doing it manually. I also tried using a set range like "H5:H6", but that caused the same problem. The data is not necessarily in order, so the first data in 2013 might not be January, etc. I thought that line with marker charts sorted data by x value. But it looks like it's just using the first 12 x values and assuming that they repeat. — Space Fish

Jon Peltier Jon Peltier · Accepted Answer · 2015-02-13T15:05:28

Make a pivot table of your date and usage values. Put date in the rows area and sum of usage in the data area. Then group date by year and month, and move year to the columns area. Make a chart of the pivot table.

enter image description here

VBA generate yearly line graph for data

1 Answers