excelvbaexcel-charts

Loop to make charts from separate ranges/tables


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

enter image description here


Solution

  • 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.