Arrange charts position in 3x5 order

0 votes

The following issue affects me. I appreciate your assistance in advance. -

I'm attempting to set up 7 charts [Doughnut] in a 3x4 arrangement, meaning I want the charts to be arranged in 3 columns and 4 rows. What my code prints in an Excel worksheet is shown below.

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
Apr 9, 2023 in Others by Kithuzzz
• 38,010 points
287 views

1 answer to this question.

0 votes

Your code was nearly complete. I believed numChartsPerRow needed to be an additional constant. Row and column calculations were also split out because they weren't quite right. These are completed in advance only as an example for debugging, but they might be completed along the same lines. 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

answered Apr 9, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

In order to learn Node.js, I should be familiar with which other technologies ?

I appreciate your decision, its the perfect ...READ MORE

answered Jul 31, 2019 in Others by ArchanaNagur
• 2,360 points
691 views
0 votes
1 answer

ERROR: You need elevated Administrator privileges in order to run this script.

Hi@akhtar, I guess you are trying to run ...READ MORE

answered Sep 8, 2020 in Others by MD
• 95,440 points
1,681 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

We can use expressions as well in ...READ MORE

answered May 31, 2022 in Others by Sohail
• 3,040 points
275 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL FIND_IN_SET function: SELECT * ...READ MORE

answered Jun 6, 2022 in Others by nisha
• 2,210 points
481 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
916 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,240 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
532 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
769 views
0 votes
1 answer

Create charts in excel sheet [ in same sheet along with data C#]

Unfortunately, it's not that simple. Use the ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
762 views
0 votes
1 answer

Insert multiple columns to the right in sequential order

Duplicate Columns Why these complications? Flexibility. You don't have ...READ MORE

answered Feb 4, 2023 in Others by narikkadan
• 63,420 points
274 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP