Error in the below macro

[Manan]

Hi,

I am getting the below error in the macro , can someone look and help me out in rectifying. Error coming on line ActiveSheet.ChartObjects("Chart 1").Activate with description as "The item with specified name not found"

Sub Chrt()
'
' Chrt Macro
'
'
  
  Dim strRng As Long
  Dim sub1 As Integer
  sub1 = 1
  strRng = Range("A" & Rows.Count).End(xlUp).Row
  strRng = strRng - sub1
  Range("Q2").FormulaR1C1 = "=MIN(R[2]C[-1]:R[" & strRng & "]C[-1])"
  
  strRng = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:P" & strRng).Select
  ActiveSheet.Shapes.AddChart.Select
  ActiveChart.SetSourceData Source:=Range("'Sheet2'!$A$1:$P$" & strRng)
  ActiveChart.ChartType = xlLine
  ActiveChart.Axes(xlValue).Select
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.Axes(xlValue).MinimumScale = 0
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.Axes(xlValue).MinimumScale = Sheets("sheet2").Range("Q2")
  
End Sub
 
[/chart]

 

[Luke M]

My guess would be that the new chart is not named "Chart 1".

However, I would suggest defining an object as your chart, and then referring to that object. Less chance of an error by not getting the right name. See below, which functions the same as yours.

Sub Chrt()
Dim strRng As Long
Dim sub1 As Integer
Dim myCH As Chart

sub1 = 1
strRng = Range("A" & Rows.Count).End(xlUp).Row

Range("Q2").FormulaR1C1 = "=MIN(R[2]C[-1]:R[" & strRng - 1 & "]C[-1])"

'Do we really need to select something here?
Range("A1:P" & strRng).Select

'Now, no matter what XL names it, we can refer to the object
Set myCH = ActiveSheet.Shapes.AddChart

With myCH
    .SetSourceData Source:=Range("'Sheet2'!$A$1:$P$" & strRng)
    .ChartType = xlLine
    .Axes(xlValue).MinimumScale = 0
    'Why do you set the minimum scale twice?
    .Axes(xlValue).MinimumScale = Sheets("sheet2").Range("Q2")
End With
End Sub