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
Edit in the main text provides the answer and correct code