CODEWITHSUNDEEP
excelvbagraphplotline
user3579146
user3579146

Reputation: 59

Plotting a line graph with a dynamic number of data rows

I am trying to plot a line graph on the summary sheet based on a dynamic number of lines on the weight tracking sheet (i.e. I add another row of data every day)

I am trying to make my line graph look exactly like the example (same colors etc.). Right now, my code compiles but the graph doesn't appear anywhere.

i am trying to make a line graph on the summary sheet based on a dynamic number of lines on the weight tracking sheet (i.e. i add more data every day)

i am trying to make my line graph look exactly like the example (same colors etc.)

Here is the spreadsheet https://drive.google.com/file/d/0B1GLuBx-ROnhSnJpdDRFTFVUbDA/edit?usp=sharing

   Private Sub WeightTrackingChart()

   'variable declaration
    Dim i As Long
    Dim LastRow As Long
    Dim WTchart As Shape
    Dim ws As Worksheet

    'Find the last used row
    LastRow = Sheets("Weight Tracking").Range("B3").End(xlUp).Row

    'Looping from fifth row till last row which has the data
    For i = 6 To LastRow
        'Prints chart to Summary sheet
        Set ws = Sheets("Weight Tracking")

        'deletes old charts
       ' ws.Shapes("WTchart").Delete

        'Adds new chart to the sheet
        Set WTchart = ws.Shapes.AddChart(xlLine, 15, 750, 500, 400)

        'Sets chart name
        WTchart.Name = "WTchart"

        'now the line chart is added...setting its data source here
        With Sheets("Weight Tracking")
            WTchart.SetSourceData Source:=.Range(.Cells(i, 1), .Cells(i, "F"))
        End With


        Next

End Sub

Upvotes: 1

Views: 1957

Answers (1)

Kyle Mac
Kyle Mac

Reputation: 146

First off I'm not a huge fan of using Cells(x,y), it's just this personal issue so you will see I have written it how I feel comfortable but feel free to adjust as you please. It appears as though your loop was creating many charts instead of adding a new series to the existing. For this I have changed where the loop begins. Additionally, I am also activating the chart to allow for the use of ActiveChart in my code. I do not know a way around this, if someone else does that would be awesome to learn.

Another thing to note for future use, make sure you are looping properly. When you were setting the loop to run until the last row you were actually saying run 7 times instead of only 5. Also, this should be the length down to go for data, not times to loop (series to create). For the length I just take the difference between your first row and last row and tell it to offset that far down.

Anyways, I hope this helps you. It worked wonderfully for me. 

Private Sub WeightTrackingChart()

   'variable declaration
    Dim i As Long
    Dim LastRow As Long
    Dim WTchart As Shape
    Dim ws As Worksheet

    'Find the last used row
    LastRow = Sheets("Weight Tracking").Range("B3").End(xlDown).Row
    Length = LastRow - Sheets("Weight Tracking").Range("B3").Row

        'Prints chart to Summary sheet
        Set ws = Sheets("Weight Tracking")

        'deletes old charts
       ' ws.Shapes("WTchart").Delete

        'Adds new chart to the sheet
        Set WTchart = ws.Shapes.AddChart(xlLine, 15, 150, 500, 400)

        'Sets chart name
        WTchart.Name = "WTchart"

        'now the line chart is added...setting its data source here

        With Sheets("Weight Tracking")

            'Looping from fifth row till last row which has the data
            For i = 0 To 4
                WTchart.Select
                ActiveChart.SeriesCollection.NewSeries
                ActiveChart.SeriesCollection(i + 1).Name = .Range("B2").Offset(0, i + 1)
                ActiveChart.SeriesCollection(i + 1).Values = Range(.Range("B2").Offset(1, i + 1), .Range("B2").Offset(Length + 1, i + 1))
                ActiveChart.SeriesCollection(i + 1).XValues = Range(.Range("B3"), .Range("B3").Offset(Length, 0))
            Next
        End With
 End Sub

PS Major kudos for posting your spreadsheet on Google Drive, made my life so much easier.

Upvotes: 1

Related Questions