I am working with a large data set that contains around 17k values and I need to make a chart for every 29 values. For instance if the data set is of 58 values, I need to make a chart for every 29 values and thus make two charts.
I have written the following code to make the charts:
'''
Sub Charts()
'
' Charts Macro
'
'
Range("D3:D32").Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$D$3:$D$32")
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).Name = "=""Marginal Costs"""
ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$C$3:$C$32"
Range("D32:D61").Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$D$32:$D$61")
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).Name = "=""Marginal Costs"""
ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$C$32:$C$61"
End Sub
'''
However as you can imagine, this is quite a tedious step for 17k values.
Can someone please suggest an easier way of making charts for the data? I would be deeply greatful for the same!
CodePudding user response:
Nested for loop is how I've handled similar problems in the past. Something like
For i=1 to 17k/29
create chart
for k=1 to 29
range = i*k
data = range
Assuming your data is sequential and in same range each time you just have to figure out the actual steps for a single set then let it go.
CodePudding user response:
Use a For/Next Loop with Step size of 29.
Option Explicit
Sub ChartsMacro()
Const SIZE = 29
Dim ws As Worksheet, cht As Chart, c, sTitle As String
Dim lastrow As Long, r As Long, n As Long, L As Long, T As Long
Dim t0 As Single: t0 = Timer
Application.ScreenUpdating = False
Set ws = ActiveSheet
With ws
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
For r = 3 To lastrow Step SIZE
n = n 1
Application.StatusBar = "Chart " & n
' position and title chart
sTitle = "Chart " & n & " (" & r & " to " & r SIZE & ")"
L = .Range("F" & r 1).Left
T = .Range("F" & r 1).Top
.Range("C" & r).Resize(, 10).Borders(xlEdgeTop).Color = vbBlack
' create chart
Set cht = .Shapes.AddChart2(227, xlLine, Left:=L, Top:=T).Chart
cht.SetSourceData Source:=.Range("D" & r).Resize(SIZE 1)
cht.ChartTitle.Text = sTitle
cht.FullSeriesCollection(1).Name = "=""Marginal Costs"""
cht.FullSeriesCollection(1).XValues = .Range("C" & r).Resize(SIZE 1)
Next
End With
Application.ScreenUpdating = True
Application.StatusBar = "Done"
MsgBox n & " Charts created in " & Format(Timer - t0, "0.0") & " secs"
End Sub