excelvbaexcel-charts

Arrange charts position in 3x5 order


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. enter image description here

I want it printed in this fashion:

enter image description here

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

Solution

  • 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
    

    enter image description here

    enter image description here