I have the following problem. Thank you in advance for the help -
I am trying to arrange 7 charts[Doughnut] in a 3x4 fashion i.e., I want 3 columns and 4 rows of the charts. Below is what my code is printing in excel worksheet.
I want it printed in this fashion:
This is the code that I have:
Const TopAnchor As Long = 8
Const LeftAnchor As Long = 140
Const HorizontalSpacing As Long = 3
Const VerticalSpacing As Long = 3
Const ChartHeight As Long = 125
Const ChartWidth As Long = 210
Dim Counter As Long
Counter = 0 Counter = Counter + 1
With ActiveChart.Parent
.Top = TopAnchor + (WorksheetFunction.RoundUp(Counter / 3, 0) - 1) * (VerticalSpacing + ChartHeight)
.Left = LeftAnchor + ((Counter) Mod 4) * (HorizontalSpacing + ChartWidth)
.Height = 125
.Width = 200
End With
Your code was almost there. I felt an additional constant needed of numChartsPerRow
. I also separated the calculation of row and column, as they weren't quite ideal. They are done in advance just for illustration when debugging, but could be done within the same lines of .Top =
or .Left=
Fun question. And my answer was less than a minute behind Tim's.....
Sub adjustCharts()
Const numChartsPerRow = 3
Const TopAnchor = 8
Const LeftAnchor = 140
Const HorizontalSpacing = 3
Const VerticalSpacing = 3
Const ChartHeight = 125
Const ChartWidth = 210
Dim ws As Worksheet, Counter As Long, zChartSet As ChartObject, _
colPos As Long, rowNumber As Long
Set ws = ActiveSheet '<--- your worksheet
For Each zChartSet In ws.ChartObjects
rowNumber = Int(Counter / numChartsPerRow)
colPos = Counter Mod numChartsPerRow
With zChartSet
.Top = TopAnchor + rowNumber * (VerticalSpacing + ChartHeight)
.Left = LeftAnchor + colPos * (HorizontalSpacing + ChartWidth)
.Height = ChartHeight
.Width = ChartWidth
End With
Counter = Counter + 1
Next zChartSet
End Sub