Not able to plot Line Chart Row wise For VBA/Excel

Question

Before Starting my problem : I have already tried the available accepted solutions in this community (Creating Line Chart for each Row using VBA excel (Dynamic Row,Column) and Create a new chart for each row using VBA Macros in Excel). For both of these my charts are creating with each line assuming the data column wise.

So I have a data table which usually has values from A1 to F12. The Column A , contains the property name like net profit, sales, cost etc whose chronological progression chart is to be plotted. The Row B2 to F2 contains the year values. The data area is from B3 to F12 which contains the values. In short each row is the changing trend of that particular property. I would like to create a line chart showing this trend (each line in the line chart represents the values in a particular row).

I was able to record a macro which does this very thing

    Worksheets("CashFlow").Activate
    Range("B5:F7").Select
    ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range("'CashFlow'!$B$5:$F$7")
    ActiveSheet.Shapes("Chart 1").IncrementLeft -26.25
    ActiveSheet.Shapes("Chart 1").IncrementTop 133.5
    ActiveWindow.ScrollRow = 2
    ActiveWindow.SmallScroll Down:=5
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Cash (In Cr)"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Cash (In Cr)"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 12).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(5, 8).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select
    ActiveChart.SetElement (msoElementChartTitleNone)
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveChart.FullSeriesCollection(1).XValues = "='CashFlow'!$B$2:$F$2"

However when I am actually running this via macro call (VBA) , it is plotting column wise.

Similarly, the above linked two answers are also charting it column wise when I use their code snippet. How do I write a VBA sub procedure that plots line chart by assuming the data is row wise.

Dy.Lee · Accepted Answer · 2020-11-24 15:57:30Z

1

I haven't been able to pinpoint your intentions, but here's an example.

Sub test()
    Dim Ws As Worksheet
    Dim rngDB As Range
    Dim rngX As Range, rngY As Range
    Dim i As Integer
    Dim l As Single, t As Single
    Dim w As Single, h As Single
    Dim objChart As ChartObject
    
    Set Ws = Sheets("CashFlow")
    
    For Each objChart In Ws.ChartObjects
        objChart.Delete
    Next objChart
    
    Set rngDB = Ws.Range("a2")
    
    l = 1 'left
    t = Ws.Range("a14").Top 'top
    w = 400 'width
    h = 200      'height
    
    For i = 1 To 10
        Set rngX = rngDB.Offset(0, 1).Resize(1, 5)
        Set rngY = rngDB.Offset(i, 1).Resize(1, 5)
        addmyCharts Ws, rngX, rngY, l, t, w, h
        t = t + h
    Next i
    
End Sub


Private Sub addmyCharts(Ws As Worksheet, rngX As Range, rngY As Range, l As Single, t As Single, w As Single, h As Single)
    Dim shp As Shape
    Dim Cht As Chart
    Dim Srs As Series
    
    Set shp = Ws.Shapes.AddChart(xlLineMarkers, l, t, w, h)
    Set Cht = shp.Chart

    With Cht
        .HasLegend = True
        For Each Srs In .SeriesCollection
            Srs.Delete
        Next Srs
        
        Set Srs = .SeriesCollection.NewSeries
        With Srs
            .XValues = rngX
            .Values = rngY
        End With
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cash (In Cr)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Cash (In Cr)"
    End With
    
End Sub

Sub testChart()
    Dim Ws As Worksheet
    Dim rngDB As Range, Rng As Range
    Dim rngX As Range, rngY As Range
    Dim shp As Shape
    Dim Cht As Chart
    Dim Srs As Series
    Dim l As Single, t As Single
    Dim w As Single, h As Single
    Dim objChart As ChartObject
  
    Set Ws = Sheets("CashFlow")
    
    For Each objChart In Ws.ChartObjects
        objChart.Delete
    Next objChart
    
    Set rngDB = Ws.Range("a3: a12")
    
    l = 1 'left
    t = Ws.Range("a14").Top 'top
    w = 400 'width
    h = 200      'height

    Set shp = Ws.Shapes.AddChart(xlLineMarkers, l, t, w, h)
    Set Cht = shp.Chart

    With Cht
        .HasLegend = True
        For Each Srs In .SeriesCollection
            Srs.Delete
        Next Srs
        
        For Each Rng In rngDB
            Set Srs = .SeriesCollection.NewSeries
            Set rngX = Ws.Range("b2").Resize(1, 5)
            Set rngY = Rng.Offset(0, 1).Resize(1, 5)
            With Srs
                .Name = Rng
                .XValues = rngX
                .Values = rngY
            End With
        Next Rng
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cash (In Cr)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Cash (In Cr)"
    End With
End Sub

edited Nov 24, 2020 at 15:57

answered Nov 23, 2020 at 1:41

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Echo Over a year ago

Hi, Thanks I will try to implement this and get back to you. In simple term, I want to create line chart for multiple rows and the data are arranged in the sheet row wise.

Echo Over a year ago

Just implemented it. It worked for me thanks. I had to make changes like putting the chart creation outside the loop as I wanted a single chart for all rows. The SeriesCollection was a completely new concept for me. Thanks again. Approving this as the valid answer.

Dy.Lee Over a year ago

@Echo, Thank you. I have not fully grasped your intentions.Just insert the collection line repeatedly.

Echo Over a year ago

Should I edit your answer and put an additional block towards the end to show how the final code for my use case actually looked like ? Maybe you would understand my intention better than and also help me formulate this question in a comprehensible manner ?

Dy.Lee Over a year ago

@Echo, I added code Sub testChart() .Please test it.

|

Jon Peltier · Accepted Answer · 2020-12-02 03:17:10Z

0

Try this:

ActiveChart.SetSourceData Source:=Range("'CashFlow'!$B$5:$F$7"), PlotBy:=xlRows

answered Dec 2, 2020 at 3:17

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

Collectives™ on Stack Overflow

Not able to plot Line Chart Row wise For VBA/Excel

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related