Excel: Creating Line chart, from data every three lines.


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
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 :)


This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of September 26, 2014.


For more resources for Microsoft Excel