Create If Next loop with multiple variables (working code)

Question

I have a table that I use to dynamically adjust a chart.

For this I use the following code which works as intended but since I am new to VBA I have difficulties creating a loop here so that I don't have to list every single series / category.

In total there are 8 series and 17 categories.
- The series filters (true / false) are listed in "B27:B34",
- the category filters are listed in "C25:S25",
all on Worksheets(3).

Can someone help me with this ?

My code (shortened):

Sub UpdateChart()
    Dim varSeries1 As String
    Dim varSeries2 As String
    Dim varSeries3 As String
    ' ...
    Dim varCategory1 As String
    Dim varCategory2 As String
    Dim varCategory3 As String
    ' ...

    varSeries1 = Worksheets(3).Cells(27, 1).Value
    varSeries2 = Worksheets(3).Cells(28, 1).Value
    varSeries3 = Worksheets(3).Cells(29, 1).Value
    ' ...
    varCategory1 = Worksheets(3).Cells(25, 3).Value
    varCategory2 = Worksheets(3).Cells(25, 4).Value
    varCategory3 = Worksheets(3).Cells(25, 5).Value
    ' ...

    Worksheets(3).ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).IsFiltered = varSeries1
    ActiveChart.FullSeriesCollection(2).IsFiltered = varSeries2
    ActiveChart.FullSeriesCollection(3).IsFiltered = varSeries3
    ' ...
    ActiveChart.ChartGroups(1).FullCategoryCollection(1).IsFiltered = varCategory1
    ActiveChart.ChartGroups(1).FullCategoryCollection(2).IsFiltered = varCategory2
    ActiveChart.ChartGroups(1).FullCategoryCollection(3).IsFiltered = varCategory3
    ' ...

R3uK · Accepted Answer · 2015-11-26 17:37:21Z

1

Using arrays :

Sub UpdateChart()
    Dim arrSeries() As Boolean
    Dim arrCategory() As Boolean
    Dim i As Integer

    arrSeries = Worksheets(3).Range("B27:B34").Value
    arrCategory = Worksheets(3).Range("C25:S25").Value

    Worksheets(3).ChartObjects("Chart 1").Activate

    For i = LBound(arrSeries, 1) To UBound(arrSeries, 1)
        ActiveChart.FullSeriesCollection(i).IsFiltered = arrSeries(i, 1)
    Next i
    For i = LBound(arrCategory, 2) To UBound(arrCategory, 2)
        ActiveChart.FullSeriesCollection(i).IsFiltered = arrCategory(1, i)
    Next i
End Sub

edited Nov 26, 2015 at 17:37

answered Nov 26, 2015 at 17:09

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

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

6 Comments

keewee279 Over a year ago

Thanks a lot for this - this looks great but when I run it the debugger starts highlighting the ActiveChart... line in the first for loop. Run-time error 13 - type mismatch.

Vincent G Over a year ago

You might have to use CBool(arrSeries(i, 1)) and CBool(arrCategory(1, i))

keewee279 Over a year ago

@VincentG: Thanks - I tried this with the first for line but then also get an error: Subscript out of range.

Vincent G Over a year ago

If FullSeriesCollection is zero-based, since the arrays are 1-based you will need to substract the LBound from i.

R3uK Over a year ago

@VincentG : it seems to be 1-based too. Changed it to include typing as Boolean, let me know if that still doesn't work, you may have to include VincentG proposition too.

|

Collectives™ on Stack Overflow

Create If Next loop with multiple variables (working code)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related