Creating Line chart, from data every three lines.

[Arithos]

Hi,

I'm trying to create a macro to make a line chart. However the buildup of the data creates some problems.

The Data:

Company	Date	Price 1	Price 2
1	10.09.2014	1204,99	1199,77
2	10.09.2014	1196,64	1191,45
3	10.09.2014	1196,68	1191,49
1	09.09.2014	1206,38	1203,29
2	09.09.2014	1198,05	1194,98
3	09.09.2014	1198,08	1195,01
1	08.09.2014	1202,83	1205,30
2	08.09.2014	1194,56	1197,02
3	08.09.2014	1194,57	1197,04
1	05.09.2014	1205,30	1199,49
2	05.09.2014	1197,11	1191,34
3	05.09.2014	1197,09	1191,34
1	04.09.2014	1202,60	1205,42
2	04.09.2014	1194,45	1197,26
3	04.09.2014	1194,44	1197,26
1	03.09.2014	1199,37	1197,59
2	03.09.2014	1191,30	1189,52
3	03.09.2014	1191,27	1189,51
1	02.09.2014	1187,20	1185,29
2	02.09.2014	1179,23	1177,33
3	02.09.2014	1179,21	1177,31
1	01.09.2014	1190,04	1189,27
2	01.09.2014	1182,09	1181,32
3	01.09.2014	1182,07	1181,30

<tbody>
</tbody>

What I want is to make three charts, one for Company 1, 2 and 3.

X-axis will have the Date as the value. and I want Price 1 and 2 to be displayed as a separate lines.

The problem arises when I want to select data from every three lines. I want to make it dynamic aswell, so that if I add new data, I can run the macro again, or it will update itself.

One thing that could help me on the way is either letting me know how to make a line-chart from an array. I populate it like this.

    LR1 = Cells(Rows.Count, 1).End(xlUp).Row
    LR = (LR1 / 3) + 1
    
    ReDim XArray(1 To LR, 1 To 2)
    ReDim Yarray(1 To LR)
    x = 1
    
For i = 1 To LR1 Step 3
    XArray(x, 1) = Cells(i, 3).Value
    XArray(x, 2) = Cells(i, 4).Value
    Yarray(x) = Cells(i, 2).Value
    x = x + 1
Next

But how to I create a chart from these values?

another option is this recorded piece:

    Range("B1:C30").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Global'!$B$11:$C$41")
    ActiveChart.ChartType = xlLine
    ActiveChart.Axes(xlCategory).Select
    ActiveSheet.ChartObjects("Chart 4").Activate
    Selection.TickLabels.NumberFormat = "dd.mm.åå;@"
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.NumberFormat = "# ##0_ ;[Red]-# ##0\ "

I could manually select the data I want, but that's a nuisance when the data increase. Is there any way to "loop information to a range"? If so that could be a solution.

Any help appreciated

 

[Arithos]

You got it...
Sorry for the delay, was on my way home.
Would the solution work for you?

Sent for my phone.

Indeed it did, took some time to wrap my head around some of it since it was new to me. But very nice work

 

[cyrilbrd]

Indeed it did, took some time to wrap my head around some of it since it was new to me. But very nice work

Glad it worked for you, thanks for the feedback...

 

[Jon Peltier]

A more flexible approach is to convert your input data into a Table, so any added data expands the Table. Then use this Table as the source data for a pivot Table. Put Price 1 and Price 2 into the data area of the Pivot Table, put Company into the column area, and put the date into the row area. Finally make a pivot chart from the pivot table. The pivot table has one row per date, so the pivot chart makes sense.

 

[cyrilbrd]

A more flexible approach is to convert your input data into a Table, so any added data expands the Table. Then use this Table as the source data for a pivot Table. Put Price 1 and Price 2 into the data area of the Pivot Table, put Company into the column area, and put the date into the row area. Finally make a pivot chart from the pivot table. The pivot table has one row per date, so the pivot chart makes sense.

Thanks for the idea, although based on your expertise wouldn't it (the pivot table) be a bit heavier (file size) than the formula and named ranges?
Also Pivot Chart are a feature reserved to PC right? Hence not compatible with a MAC although OP might probably uses Excel 2010...

 

[Jon Peltier]

1. Really worried about file size? Office 2007-2013 files (including Office 2011) are essentially zipped; hard drive space costs what, pennies per gigabyte? And most everyone has broadband now and ginormous email accounts. And pivot tables are fast and flexible.

2. I didn't realize Macs don't do Pivot Charts. I use Mac Excel as little as possible, but I had to check it out. Hmmm. In any case, the pivot table can still be used to make a regular chart which is no less capable than one made from data that involves complicated formulas.

 

[Arithos]

1. Really worried about file size? Office 2007-2013 files (including Office 2011) are essentially zipped; hard drive space costs what, pennies per gigabyte? And most everyone has broadband now and ginormous email accounts. And pivot tables are fast and flexible.

2. I didn't realize Macs don't do Pivot Charts. I use Mac Excel as little as possible, but I had to check it out. Hmmm. In any case, the pivot table can still be used to make a regular chart which is no less capable than one made from data that involves complicated formulas.

This sounds interresting, I'll check the pivot option out. The amount of array formulas slows the sheet down abit, however that is not much of a problem since this sheet is mainly for statistical overview.

Thanks!

 

[Arithos]

For those who are interested, this ended as my solution.

Mix of recorded code and adjustments.

If anyone have simplifications/how I could manually enter the recorded parts, I would love to know (this is only for educational purposes).

Thanks for all the help!

Option Explicit


Sub MakeCharts2()
Application.ScreenUpdating = False


On Error Resume Next
Dim Piv As Worksheet, ChSht As Worksheet
    Sheets("Pivot3").Visible = True   'Data chart for pivot table
    Sheets("ChartSht3").Visible = True
    On Error GoTo 0
Set Piv = Sheets("Pivot3")
Set ChSht = Sheets("ChartSht3")
ChSht.Cells.Clear
Piv.Cells.Clear


Dim sht1 As Worksheet


Set sht1 = Sheets("Vekst")


sht1.Select
On Error Resume Next
    ActiveSheet.ChartObjects("Main").Delete
    ActiveSheet.ChartObjects("SkagA").Delete
    ActiveSheet.ChartObjects("SkagB").Delete
On Error GoTo 0


Piv.Cells.Clear


    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Vekst!Table3", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Pivot3!R1C1", TableName:="PivotTable6", DefaultVersion _
        :=xlPivotTableVersion12
    Piv.Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("NAV Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Fund")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("NAV"), "Sum of NAV", xlSum
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("Official NAV"), "Sum of Official NAV", xlSum
Dim LR As Integer
Dim Length As Integer, Witdth As Integer


LR = Piv.Cells(Rows.Count, 1).End(xlUp).Row


Length = LR - 3


Piv.Select
    Piv.Range(Cells(3, 1), Cells(LR - 1, 3)).Copy ChSht.Cells(1, 1)
    Piv.Range(Cells(3, 4), Cells(LR - 1, 5)).Copy ChSht.Cells(1, 6)
    Piv.Range(Cells(3, 6), Cells(LR - 1, 7)).Copy ChSht.Cells(1, 10)
    Piv.Range(Cells(3, 1), Cells(LR - 1, 1)).Copy ChSht.Cells(1, 5)
    Piv.Range(Cells(3, 1), Cells(LR - 1, 1)).Copy ChSht.Cells(1, 9)




ChSht.Select
On Error Resume Next
ActiveSheet.ChartObjects("Main").Delete
ActiveSheet.ChartObjects("SkagA").Delete
ActiveSheet.ChartObjects("SkagB").Delete
On Error GoTo 0


  Range("A1:C" & Length).Select


    ActiveSheet.Shapes.AddChart.Name = "Main"
    ActiveSheet.ChartObjects("Main").Activate
    
With ActiveChart
    .SetSourceData Source:=Range("'ChartSht'!$A$1:$C$" & Length)
    .ChartType = xlLine
    .ApplyLayout (1)
    .ChartTitle.Text = "Main Fund" '******************
    .Axes(xlValue).AxisTitle.Delete
    .SeriesCollection(1).Name = "=""NAV"""
    .SeriesCollection(2).Name = "=""Official NAV"""
End With


Range("E1:G" & Length).Select
    ActiveSheet.Shapes.AddChart.Name = "SkagA"
    ActiveSheet.ChartObjects("SkagA").Activate
    
    With ActiveChart
            .SetSourceData Source:=Range("'ChartSht'!$E$1:$G$" & Length)
            .ChartType = xlLine
            .ApplyLayout (1)
            .ChartTitle.Text = "Global A" '******************
            .Axes(xlValue).AxisTitle.Delete
            .SeriesCollection(1).Name = "=""NAV"""
            .SeriesCollection(2).Name = "=""Official NAV"""
    End With
    
    Range("I1:K" & Length).Select
        ActiveSheet.Shapes.AddChart.Name = "SkagB"
    ActiveSheet.ChartObjects("SkagB").Activate


With ActiveChart
    .SetSourceData Source:=Range("'ChartSht'!$I$1:$K$" & Length)
    .ChartType = xlLine
    .ApplyLayout (1)
    .ChartTitle.Text = "Global B" '******************
    .Axes(xlValue).AxisTitle.Delete
    .SeriesCollection(1).Name = "=""NAV"""
    .SeriesCollection(2).Name = "=""Official NAV"""
End With
   
ActiveSheet.ChartObjects("Main").Copy


sht1.Select
    Range("D1").Select
        ActiveSheet.Paste
        
ChSht.Select
    ActiveSheet.ChartObjects("SkagA").Copy


sht1.Select
    Range("G1").Select
        ActiveSheet.Paste
        
ChSht.Select
    ActiveSheet.ChartObjects("SkagB").Copy


sht1.Select
    Range("J1").Select
        ActiveSheet.Paste
          Sheets("Pivot3").Visible = False
    Sheets("ChartSht3").Visible = False
       
 Application.ScreenUpdating = True
End Sub