Item with the specified name was found error

[Crypto55]

While generating a chart from some Excel data I get a run-time error "The Item with the specified name wasn't found".

This line of code is highlighted in debug:

ActiveSheet.Shapes("Chart 2").IncrementLeft -3

I need to be able to force the created chart to be named a specific name each time so whenever I run the macro it will always work. I had this issue when my first macro created a new sheet after formatting the text data. I solved that by forcing the new sheet to always be named Sheet2 by adding this line of code in and the macro works great:

ActiveSheet.Name = "Sheet2"

I can't get this working for the chart though.

I have tried:

ActiveChart.Name = "Chart 2"

ActiveChart.Parent.Name = "Chart 2" (even though this is not an embedded chart)

What can I add to the code just before the

ActiveSheet.Shapes("Chart 2").IncrementLeft -3 line

to get past the chart naming issue?

Thanks all,
Don

 

[Jerry Sullivan]

Welcome to MrExcel, Don.

Please post your entire code, so we can help you more effectively.

 

[Crypto55]

Hi Jerry, appreciate it!

I just got that part working!

With my force sheet rename this line worked:

ActiveChart.SetSourceData Source:=Range("Sheet2!$B:$B,Sheet2!$D:$D")

Thanks all!
Don

 

[Crypto55]

Well I guess I spoke too soon. It worked once but now is doing the same thing - "The Item with the specified name wasn't found" The item is listed in the BOLD text below.

[/COLOR]Sub Macro14()
'
' Macro14 Macro
'
 
'
    Range("B:B,D:D").Select
    Range("TestD2[[#Headers],[Core Temperature]]").Activate
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=Range("Sheet2!$B:$B,Sheet2!$D:$D")
    [B]ActiveSheet.Shapes("Chart 2").IncrementLeft -3[/B]
    ActiveSheet.Shapes("Chart 2").IncrementTop -153
    ActiveSheet.Shapes("Chart 2").ScaleWidth 2.0520833333, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 2").ScaleHeight 1.9930555556, msoFalse, _
        msoScaleFromTopLeft
    Range("S1").Select
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveSheet.Shapes("Chart 2").ScaleWidth 0.891370665, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 2").ScaleHeight 0.893728223, msoFalse, _
        msoScaleFromTopLeft
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "TIME"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "TIME"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 4).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    ActiveChart.PlotArea.Select
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "DEGREE F"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "DEGREE F"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 8).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 8).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    ActiveChart.PlotArea.Select
    ActiveChart.SetElement (msoElementLegendRight)
    Range("T20").Select
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveSheet.Shapes("Chart 2").IncrementLeft 34.5
    ActiveSheet.Shapes("Chart 2").IncrementTop -1.5
    Range("E20").Select
End Sub

Don

 

[Jerry Sullivan]

Hi Don,

You noted that this isn't an embedded chart, but the code implies that it is. An embedded chart just means a chart that is within a worksheet, as opposed to a sheet that only consists of a chart. Can you confirm that what you have is an embedded chart?

If so, then this code should work to rename the chartobject.

ActiveChart.SetSourceData Source:=Range("Sheet2!$B:$B,Sheet2!$D:$D")
ActiveChart.Parent.Name = "Chart 2"

You said that statement didn't work for you? What error do you get when you try that?

 

[Crypto55]

Currently I'm getting the chart created as a sheet chart object and not embedded (comes in as a new sheet would). The section in bold is now highlighted in debug As stated I get the chart now, named Chart 1 as a new workbook sheet but it displays a run-time 91 error - object variable or with block variable not set. Code doesn't lie something with that WITH statement.

Sub Macro17()
'
' Macro17 Macro
'


'
Range("B:B,D:D").Select
Range("TestD2[[#Headers],[Core Temperature]]").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Range("Sheet2!$B:$B,Sheet2!$D:$D")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Name = ("Chart 1")
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
ActiveSheet.Shapes("Chart 1").IncrementLeft 58.5
ActiveSheet.Shapes("Chart 1").IncrementTop -21.75
ActiveChart.ChartArea.Copy
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -129
ActiveSheet.Shapes("Chart 1").IncrementTop -103.5
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.5645833333, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.6145833333, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.0625833622, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0580647419, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft 1.5
ActiveSheet.Shapes("Chart 1").IncrementTop 45.75
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Time"

End Sub