Search code examples
vbaloopschartsoffset

VBA Combine integer with chart loop iteration


I am looking to change the formatting of a range of bar charts based on a set of dynamic xy variables. I've managed to set up a loop that correctly formats a single chart - selecting the correct columns that contain the data and then amending the selection so that any empty rows are omitted.

What I am trying to do now is to create a loop that applies the correct formatting to each of the 13 charts embedded in the sheet. Each chart will read off its own set of three columns (X variables and 2 x Y variables) that will contain data ranges that vary from chart to chart. I had aimed to do this using range.offset and an integer variable (j2 in the code below) that increases with each iteration to shift the column references to the right by one for every iteration of the loop.

I will need this variable to loop simultaneously with the chart loop - is it possible to derive a variable from the number charts previously activated in a loop or the number of iterations that have passed?

The line in question is 'j2 = j1 + in the code below (Now Removed).

****I've now solved the issue, largely by adding a simple counter (j1) and moving the lastrow variable inside the loop structure. The completed code can be seen below:****

    Sub ModifyChart()
Dim AllCatRange As Range
Dim lastRow As Long
Dim iLoop As Long
Dim PlotSerRange As Range
Dim PlotSer2Range As Range
Dim PlotCatRange As Range
Dim ws As Worksheet
Dim objCht As ChartObject
Dim j1 As Integer

Set ws = Worksheets("Indicator Summary")
j1 = -3

 With ws
     For Each objCht In ws.ChartObjects
        lastRow = 5
        j1 = j1 + 3
        Set AllCatRange = Worksheets("Indicator Summary").Range("J6:J50").Offset(0, j1)

        For iLoop = 1 To 45
            If AllCatRange.Cells(iLoop, 1) <> "" Then
            lastRow = lastRow + 1
            End If
        Next iLoop

        Set PlotCatRange = .Range("J6:J" & lastRow).Offset(0, j1)
        Set PlotSerRange = .Range("K6:K" & lastRow).Offset(0, j1)
        Set PlotSer2Range = .Range("L6:L" & lastRow).Offset(0, j1)

        With objCht
        .Chart.SeriesCollection(1).XValues = "=" & PlotCatRange.Address(False, False, xlA1, xlExternal)
        .Chart.SeriesCollection(1).Values = "=" & PlotSerRange.Address(False, False, xlA1, xlExternal)
        .Chart.SeriesCollection(2).XValues = "=" & PlotCatRange.Address(False, False, xlA1, xlExternal)
        .Chart.SeriesCollection(2).Values = "=" & PlotSer2Range.Address(False, False, xlA1, xlExternal)

        Set AllCatRange = Nothing
        Set PlotCatRange = Nothing
        Set PlotSerRange = Nothing
        Set PlotSer2Range = Nothing

        End With

    Next objCht
    End With

End Sub

Thanks, Ant


Solution

  • Edit in the main text provides the answer and correct code