Charts in VBA script need to align per loop

[4dplane]

Hi,

I have a for loop in VBA that creates charts and assigns them a width, height, left and top value:

Range("A" + CStr(dayend) + ":D" + CStr(daystart)).Select
    Range("A" + CStr(daystart)).Activate
   
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Test'!$A$" + CStr(dayend) + ":$D$" + CStr(daystart))
    ActiveChart.ChartType = xlStockOHLC
    ActiveChart.HasAxis(xlCategory) = True
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).TickMarkSpacing = 25
    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
    Selection.MajorTickMark = xlCross
    Selection.TickLabelPosition = xlNone
    ActiveChart.PlotArea.Select
    ActiveChart.ClearToMatchStyle
    ActiveChart.ChartStyle = 35
    ActiveChart.ClearToMatchStyle
    
    ActiveChart.ChartArea.Width = 600
    ActiveChart.ChartArea.Height = 400
    ActiveChart.ChartArea.Left = 1000
    ActiveChart.ChartArea.Top = 10

    Range("H" + CStr(dayend) + ":I" + CStr(daystart)).Select
    Range("H" + CStr(daystart)).Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Test'!$H$" + CStr(dayend) + ":$I$" + CStr(daystart))
    'ActiveChart.SetSourceData Source:=Range("'Test'!$H$97:$I$192")'
    ActiveChart.ChartType = xlSurface
    ActiveWindow.SmallScroll Down:=12
    ActiveChart.HasAxis(xlCategory) = True
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).TickMarkSpacing = 25
    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
    Selection.MajorTickMark = xlCross
    Selection.TickLabelPosition = xlNone
    ActiveChart.ChartArea.Top = 420
    ActiveChart.ChartArea.Width = 600
    ActiveChart.ChartArea.Height = 400
    ActiveChart.ChartArea.Left = 1000

This is not the full code, its missing the for loop...

The problem is the charts are all stacked on top of each other because of the Top property in the ActiveSheet. If I remove the ActiveSheet.ChartObjects.Top the charts line up along the right as I want but the two charts that are created per loop are on top of each other. So per loop I have two charts that that ActiveChart.ChartArea.Left = 1000 and this works perfectly but when I use the ActiveChart.ChartArea.Top = 420 and ActiveChart.ChartArea.Top = 10 all of the charts per loop are at the top 10 and 420.

In stick view I want this: Each return is a row
- Chart
- Chart
-
-
-
-
- Chart
- Chart

If I remove the top parameter I get this: Where there are two charts below each word "Chart"

- Charts
-
-
-
-
-
- Charts

And I use the code above I get this: Where all the charts regardless of the numner of loops are at the top stacked on top of each other.
- Chart
- Chart
-
-
-
-
-
-
-
-
-
-

Any help would be greatly appreciated.

Thanks,
4dplane

 

[Misca]

Try something like this:

Dim ChO As ChartObject
Dim i As Integer
Dim Rng As Range
Set Rng = ActiveSheet.Range("G20")
i = 20
For Each ChO In ActiveSheet.ChartObjects
    With ChO
        .Top = Rng.Top
        .Left = Rng.Left
    End With
    
    Set Rng = Rng.Offset(i, 0)
    
    If i = 20 Then
        i = 40
    Else
        i = 20
    End If
    
Next CH

Use the i-pattern to change the space between the charts. Keeping the i constant will spread the charts evenly.

 

[4dplane]

Thanks Misca,

I found a solution here:
http://www.experts-exchange.com/Sof...Office_Suites/MS_Office/Excel/Q_26841073.html