Coding multiple-row graphs (looping) in VBA

Question

I'm sure there is a fairly simple answer to this question but I've been pulling my hair out for multiple days trying to figure this out and no one in my department works with VBA enough to know. I'm new to automating graphs but I have a project where I need to make almost 800 graphs! The data itself is fairly straightforward, with 4 variables (columns): County, City, Store Type, and Number of Employees:

COUNTY  CITY    STORE TYPE  NUMBER OF EMPLOYEES
X   A   1   100
X   A   2   100
X   A   3   100
X   A   4   100
X   B   1   100
X   B   2   100
X   B   3   100
X   B   4   100

I need to make bar graphs for each County/City combination with the Store Type as the X-values and the Number of Employees displayed on the Y-axis. This is super easy to do in VBA:

Sub makegraph2()
Range("A2:D5").Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$2:$D$5")
ActiveChart.ChartTitle.Text = "Employees per Store Type"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Employees per Store 
Type"
End Sub

Rather than copying/pasting the same graph and reselecting the data for each 4 rows downward, I'm trying to find a way to automate it with a loop. I've tried doing this myself (testing with only 50 rows ((A2:D50 represents all 4 columns and 50 rows)) so not to get 800 graphs that are wrong and crash my computer) using offset in many different ways. My most resent attempt produces a graph with all 50 rows on it at once:

Sub makegraph()
Dim Row As Integer

For Row = 1 To 50
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!A2:D50").Offset(4, 0)
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Employees by Store Type"
Next Row
End Sub

Before that, I made separate graphs but they were all of the same data (would not go down 4 rows to graph the next set). So clearly I'm using Offset and Range wrong, but I can't figure out how...

Any help is so appreciated!! Thank you!!

Yes! Some Y-values are 0 (no employees), but always 4 rows for the store type. — Taylor R
– Taylor R, Commented Aug 22, 2018 at 22:21
Welcome to Stack Overflow, Taylor! Have you tried Pivot Charts? You can use filters in Pivot Charts to cut down a lot of the work here. Here's a link to a tutorial with more information - Link. — girlvsdata
– girlvsdata, Commented Aug 22, 2018 at 22:23
Hello! Yes, I've used Pivot Charts, but only for dynamic data sets of single tables--I'll look into it, thanks — Taylor R
– Taylor R, Commented Aug 22, 2018 at 23:54

Tim Williams · Accepted Answer · 2018-08-23 01:04:39Z

2

Untested, but try this:

Sub makegraph()

    Dim i As Long, co, cht As Chart '<<edit here
    Dim sht As Worksheet

    Set sht = Sheets("Sheet1")

    For i = 1 To 50
        Set co = ActiveSheet.Shapes.AddChart2(201, xlColumnClustered)
        'position and size the chart here by setting the
        '  Top/Left/Width/Height properties
        With co.Chart
            'calculate the required offset
            .SetSourceData Source:=sht.Range("A2:D5").Offset((i - 1) * 4, 0)
            .ChartTitle.Text = "Employees by Store Type"
        End With
    Next i
End Sub

edited Aug 23, 2018 at 1:04

answered Aug 22, 2018 at 22:23

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Taylor R Over a year ago

Thank you for your answer! I tried this, but I kept getting a "type mismatch" error for the line Set co = ActiveSheet.Shapes.AddChart2(201, xlColumnClustered)

Tim Williams Over a year ago

I edited the declaration for co to remove As ChartObject - that should fix it

Taylor R Over a year ago

Thank you so much!!

DaanV · Accepted Answer · 2018-08-23 07:17:47Z

1

Sub makegraph()
Dim Row As Integer

For Row = 1 To 50
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!A2:D50").Offset(4, 0)
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Employees by Store Type"
Next Row
End Sub

from what I understand by this code. the offset you apply is always from the same spot which is Range("Sheet!A2:D50") secondly you have specified the whole area as the source.

I would suggest doing more absolute values or make a variable for the range:

example of variable range:

Sub makegraph()
    Dim Row As Integer
    Dim Area As Range

    Set Area = Range("A2:D5")

    For Row = 1 To 50
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Area
        Set Area = Area.Offset(4, 0)
        ActiveChart.ChartTitle.Select
        ActiveChart.ChartTitle.Text = "Employees by Store Type"
    Next Row
End Sub

Example of Absolute values:

Sub makegraph()
    Dim Row As Integer

    For Row = 2 To 50 Step 4
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range(Cells(Row, 1), Cells(Row + 3, 1))
        ActiveChart.ChartTitle.Select
        ActiveChart.ChartTitle.Text = "Employees by Store Type"
    Next Row
End Sub

edited Aug 23, 2018 at 7:17

answered Aug 22, 2018 at 22:32

DaanV

3391 silver badge6 bronze badges

5 Comments

girlvsdata Over a year ago

Upvoting for great use of the Step Keyword! I will definitely be making use of this. Nice answer.

Taylor R Over a year ago

Thank you! The variable range makes the most sense to me, but I keep getting a "Object variable or With block variable not set" for the line Area = Range("Sheet1!A2:D5") not sure why :(

Taylor R Over a year ago

And using absolute values, I keep getting a mismatch error for the line "ActiveChart.SetSourceData Source:=Range("Sheet1!A" + Row + ":D" + (Row + 3))" related to using "Row" which I also don't understand...

DaanV Over a year ago

Oh sorry if you ever get 'the object variable or with block' error you need to use set: set Area = Range()

DaanV Over a year ago

I have edited the examples to make sure they work. Im sorry they didn't as I did them from the top of my head

Collectives™ on Stack Overflow

Coding multiple-row graphs (looping) in VBA

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related