Loop to make charts from separate ranges/tables

Question

Do you know how to make a loop in VBA to insert a chart for for example every range containing string "%" or just for every separate range as shown on picture? I tried using such code as shown on the picture but it only adds chart for the first range, not for all of them.

I tried such code:

Sub Charts()
Const SFind As String = "%"
Dim rng As Range
Dim lo As ListObjects


For Each lo In Worksheets("Wyniki").ListObjects

On Error Resume Next
Set rng = ActiveSheet.Cells.Find(SFind).CurrentRegion
If Not rng Is Nothing Then
 With ActiveSheet.Shapes.AddChart.Chart
  .ChartType = xlColumnClustered
  .SetSourceData rng
 End With
End If

Next

End Sub

[An image of your code is not helpful](http://idownvotedbecau.se/imageofcode) • Define all your seperate ranges as "tables" (ListObjects) as you did with the first 2. Then [Loop through](https://stackoverflow.com/a/27750846/3219613) those `ListObjects` for example like `For Each lo In Worksheets("Sheet1").ListObjects`. [The VBA Guide To ListObject Excel Tables](https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables) might help for more info about ListObjects. • Give that a try and if you get stuck or errors come back with your code. — Pᴇʜ, Mar 23 '22 at 12:42

score 1 · Accepted Answer · answered Mar 23 '22 at 13:07

You need to use the range from lo as data source.

Public Sub CreateChartsFromAllTables()
    Dim lo As ListObject
    For Each lo In Worksheets("Wyniki").ListObjects
        With Worksheets("Wyniki").Shapes.AddChart.Chart
            .ChartType = xlColumnClustered
            .SetSourceData lo.Range
        End With
    Next
End Sub

Never use just On Error Resume Next as you did. This is evil, as it hides all your error messages until End Sub but the errors still occur, you just cannot see their messages. If you don't see the messages but there are errors, you cannot fix them. If you don't fix them the code does not work. Never use it like this. See VBA Error Handling – A Complete Guide for how to use error handling properly.

Might be worth noting that there are several "ranges" per OP's screenshot that are not actually tables (`ListObject`s). — BigBen, Mar 23 '22 at 13:35
@BigBen As I said in my comment he needs to change them into tables too. To make this work. — Pᴇʜ, Mar 23 '22 at 14:14

Loop to make charts from separate ranges/tables

1 Answers1