VBA Getting an error when trying to move a chart

Question

I have written the following code that is used to create a chart in an excel sheet:

Sub AddChart()

Dim sh As Worksheet
Dim chrteit As Chart

Set sh = ActiveWorkbook.Worksheets("TraceTable")
Set chrteit = sh.Shapes.AddChart.Chart

lastrows = Range("A2").End(xlDown).Row

With chrteit
.ChartType = xlXYScatter
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = sh.Range(Cells(2, 6), Cells(lastrows, 6))
.SeriesCollection(1).Values = sh.Range(Cells(2, 7), Cells(lastrows, 7))

    Let chrteit.Parent.Name = "EIT"
    .Parent.Height = Range("N2:N14").Height
    .Parent.Width = Range("N2:T2").Width
    .Parent.top = Range("N2").top
    .Parent.Left = Range("N2").Left
    .Parent.Placement = xlFreeFloating

End With

Now after this code runs, a chart is created with the used data cells. I then have another macro run that will manipulate all the data and move it around, so I drew the charts to reference the cells easily before the data gets manipulated. Anyways, the cells are reformatted and adjust in size, so the graph stays where it is. All I want to then do is move the chart back to the left using:

Sub MoveChart()

With ActiveWorkbook.Worksheets("TraceTable")
.ChartObjects("EIT").Left = .Range("N2").Left
End With

End Sub

But I am getting an error that says "The item with the specified name wasn't found." and highlights the line:

.ChartObjects("EIT").Left = .Range("N2").Left

What is wrong with my code?! It worked once before but I'm not sure what I changed. Please help, thank you!

Answer 1

Use the ChartArea property of Chart object. See below:

Sub MoveChart()

    Dim Sh As Worksheet: Set Sh = ThisWorkbook.Sheets("TraceTable")
    Dim Shp As Chart

    With Sh
        Set Shp = .ChartObjects("EIT").Chart
        Shp.ChartArea.Left = .Range("N2").Left
    End With

End Sub

In any case, your separated macros can be summarized in one macro as follows.

Sub FullRun()

    Dim TSht As Worksheet
    Dim LCol As Long, LRow As Long
    Dim RoundOffR As Range, RoundOffC As Range
    Dim ShotR As Range, ShotC As Range
    Dim Cht As Chart
    Dim DivideR As Range, DivideC As Range

    Set TSht = ActiveWorkbook.Sheets("TraceTable")

    With TSht
        ' Get boundaries.
        LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        ' Get top row and fill it with color.
        With .Cells(1, 1).Resize(1, LCol)
            .Interior.Color = 14136213
            .Font.Bold = True
        End With
        ' Use Union for a cleaner delete.
        ' Columns to delete are Date, Little, Deviation, F, G
        Union(.Range("D:D"), .Range("F:F"), .Range("H:H"), .Range("O:P")).Delete
        ' Initialize the range to round off.
        Set RoundOffR = .Range("E2:K" & LRow)
        RoundOffR.NumberFormat = "0"
    End With

    ' Round the values. If it's from E column, change from "0"
    ' format to "0.0000" format.
    For Each RoundOffC In RoundOffR
        RoundOffC.Value = Application.Round(RoundOffC.Value, 0)
        If RoundOffC.Column = 11 Then
            RoundOffC.NumberFormat = "0.0000"
        End If
    Next

    ' Insert new column, distribute some values
    With TSht
        With .Range("C1")
            .EntireColumn.Insert
            .Value = "Sample"
        End With
        .Range("E1").Value = "Type"
        Set ShotR = .Range("D2:D" & LRow)
    End With

    For Each ShotC In ShotR
        With ShotC
            ' Get the last two digits of D and put in E.
            .Offset(0, 1).Value = Right(.Value, 2)
            ' Get the 10th position in D, get next two characters, and put in C.
            .Offset(0, -1).Value = Right(.Value, 2)
            ' Get the last two digits in B, and replace D.
            .Value = Right(.Offset(0, -2).Value, 2)
        End With
    Next

    ' Adjust formatting and create chart.
    With TSht
        .Cells.HorizontalAlignment = xlCenter
        .Columns.AutoFit
        .Rows.AutoFit
        Set Cht = .Shapes.AddChart.Chart
    End With

    ' Manipulate chart.
    With Cht
        .Parent.Name = "EIT"
        .ChartType = xlXYScatter
        .SeriesCollection.NewSeries
        With .SeriesCollection(1)
            .XValues = TSht.Range("F2:F" & LRow)
            .Values = TSht.Range("G2:G" & LRow)
        End With
        ' User .ChartArea for size and position.
    End With

    ' "Divide" the rows.
    Set DivideR = TSht.Range("B2:B" & LRow)
    For Each DivideC In DivideR
        ' If current cell is not empty and not equal to next cell, insert a row.
        If Not IsEmpty(DivideC) And DivideC.Value <> DivideC.Offset(1, 0).Value Then
            DivideC.Offset(1, 0).EntireRow.Insert
        End If
    Next

    ' Add borders.
    TSht.Cells.SpecialCells(xlCellTypeConstants).Borders.LineStyle = xlContinuous

    ' Resize and move chart. Use .ChartArea for this.
    With Cht.ChartArea
        .Height = TSht.Range("N2:N14").Height
        .Width = TSht.Range("N2:T2").Width
        .Top = TSht.Range("N2").Top
        .Left = TSht.Range("N2").Left
    End With

End Sub

That takes care of everything from borders, column deletion, dividers, chart creation and manipulation, and so on. This is best so that your macro doesn't jump all over the place. Of course, it uses ActiveWorkbook so you can put this in another workbook and just run it while the workbook containing the TraceTable sheet is highlighted.

Hope this helps.