Charts in VBA

[SBF12345]

I am having trouble producing charts in VBA. I am having trouble with the portion in the With statement.

I was receiving an error "Unable to get the chartobjects property of the worksheet class" on the line:

With Worksheets("LowDistCharts").ChartObjects(aa)

The properties I would like to add to the charts are: line chart type, x and y axis labeled, include a chart title, and most importantly because I would like to produce many of the charts I would like to have them added to the sheet one below the other so they are easily visible. Also, I would like to shrink the chart size to a lesser width and height.

The code I have currently is below:

Dim aa As IntegerDim StartDate As Range
Dim EndDate As Range
Dim RngStart As String
Dim RngEnd  As String


For aa = 5 To 24


    ActiveWorkbook.Sheets("AllDistanceMeasures").Activate


    Set StartDate = Cells(aa, 9)
    Set EndDate = Cells(aa, 10)


    ActiveWorkbook.Sheets("ActiveSheet").Activate
    
        RngStart = Cells.Find(What:=StartDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Address(1, 1)
        
        RngEnd = Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 1).Address(1, 1)
    
    With Worksheets("LowDistCharts").ChartObjects(aa)
    .HasTitle = True
    .ChartTitle.Text = aa & StartDate & "to" & EndDate
    '.Placement =
    .RoundedCorners = True
    .SetSourceData Source:=Range("LowDistCharts!RngStart:RngEnd")
    .ChartType = xlLine
    
    
    End With

 

[Domenic]

First, make sure that the active workbook contains a worksheet named "LowDistChart". Then, you'll need to make sure that your worksheet named "LowDistChart" contains at least 24 chartobjects. Also, some of those properties you're trying to set belong to the Chart object, not the ChartObject object. And the syntax for the range you're trying to assign to the chart is not correct either. So maybe something like this..

        RngStart = Cells.Find(What:="wk1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Address([COLOR=#ff0000]External:=True[/COLOR])
        
        RngEnd = Cells.Find(What:="wk3", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 1).Address([COLOR=#ff0000]External:=True[/COLOR])

    With Worksheets("LowDistCharts").ChartObjects(aa)
        .RoundedCorners = True
        With .Chart
            .HasTitle = True
            .ChartTitle.Text = aa & StartDate & "to" & EndDate
            .ChartType = xlLine
            .SetSourceData Source:=Range(RngStart & ":" & RngEnd)
        End With
    End With

 

[SBF12345]

I definitely did include a worksheet named "LowDistCharts" in the workbook; I definitely do not have chartobjects in the worksheet or vba code.

The line above is still yielding an error "Unable to get the chart objects property of the worksheet class". How do I add a chartobject to the worksheet? Is it correct to say that I must dimension a variable as a chartobject and then add a chart object for every iteration from which to place a chart?

 

[SBF12345]

Got it. it seems to be working with the code below. Just needs some fine tuning now. Thanks!

Set sh = ActiveWorkbook.Worksheets("LowDistCharts")    Set chrt = sh.ChartObjects.Add(Left:=chtLeft, Top:=chtTop, Width:=300, Height:=300)
    Set ch = chrt.Chart