CODEWITHSUNDEEP
vbaexcelloopsfor-loop
keewee279
keewee279

Reputation: 1654

Create If Next loop with multiple variables (working code)

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

Upvotes: 0

Views: 263

Answers (1)

R3uK
R3uK

Reputation: 14537

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

Upvotes: 1

Related Questions