Hi,
I'm trying to create a macro to make a line chart. However the buildup of the data creates some problems.
The Data:
<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.
But how to I create a chart from these values?
another option is this recorded piece:
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
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.
Code:
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:
Code:
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