Create Chart side by side using for loop

Question

I am trying to create 3 charts that I want to put side by side using a for loop but every time I run the loop multiple charts poop up but with no data on them.

Dim MyChart As Chart
Dim MyChart2 As Chart
Dim MyChart3 As Chart
Dim DataRange As Range
Dim DataRange2 As Range
Dim DataRange3 As Range


'LEFT DISTANCE IN PIXELS
lpos = 200
'TOP DISTANCE IN PIXELS
tpos = 50

' EMBED THE CHART

Set DataRange = ActiveSheet.Range("A1:B6")
'Set DataRange2 = ActiveSheet.Range("A9:B14")
'Set DataRange3 = ActiveSheet.Range("A17:B22")

'LOOP INTRODUCTION
For i = 0 To 600 Step 200
    lpos = lpos + i
Set MyChart = ActiveSheet.Shapes.AddChart2(225, xlColumnClustered, lpos, tpos, 200, 200, 1).Chart
''Set MyChart2 = ActiveSheet.Shapes.AddChart2(225, xlColumnClustered, lpos + i, tpos, 200, 200, 1).Chart
''Set MyChart3 = ActiveSheet.Shapes.AddChart2(225, xlColumnClustered, lpos + i, tpos, 200, 200, 1).Chart

Next i
    

' IMPORT DATA INTO EMBEDDED CHART
MyChart.SetSourceData Source:=DataRange
'MyChart2.SetSourceData Source:=DataRange2
'MyChart3.SetSourceData Source:=DataRange3

anytime i try to run the loop it doesnt work and have multiple charts with no data. why should I do?

chris neilsen · Answer 1 · 2022-02-15T02:36:42.760

You're close, just need to structure your range data into an array so you can reference one range for itteration, and add the source reference in the loop

Sub Demo()
    Dim ws As Worksheet
    Dim MyChart As Chart
    Dim DataRange() As Range
    Dim DataRange2 As Range
    Dim DataRange3 As Range
    Dim lpos As Long, tpos As Long, wdth As Long, hgt As Long
    Dim i As Long
    
    Set ws = ActiveSheet ' or any sheet you choose
    
    ' chart data
    lpos = 200
    tpos = 50
    wdth = 200
    hgt = 200
    
    ' source ranges
    ReDim DataRange(0 To 2)
    Set DataRange(0) = ws.Range("A1:B6")
    Set DataRange(1) = ws.Range("A9:B14")
    Set DataRange(2) = ws.Range("A17:B22")
    
    ' add charts
    For i = 0 To 2
        Set MyChart = ws.Shapes.AddChart2(225, xlColumnClustered, lpos + i * wdth, tpos, wdth, hgt, 1).Chart
        With MyChart
            ' Source
            .SetSourceData Source:=DataRange(i)
            ' Other properties, eg
            .ChartTitle.Text = "MyChart " & i + 1
        End With
    Next i
        
End Sub

Create Chart side by side using for loop

1 Answers1