1

I am getting a

'Run-time error '438' - Object doesn't support this property or method'

on one of the sub-routines which creates a chart. The macro runs fine when executing from my computer, but gives out the run-time error from another machine. The line where is fault occurs is inside ActiveSheet.Shapes.AddChart2(227, xlLine).Select

Below is the subroutine code and the line when the code faults.

    'Plot Average Temperature

    Range("BO4", Range("BO4").End(xlDown)).Select
    ***ActiveSheet.Shapes.AddChart2(227, xlLine).Select***
    ActiveChart.SetSourceData Source:=Range("BO1", Range("BO4").End(xlDown))
    'ActiveChart.AutoScaling = True
    ActiveChart.Parent.Cut
    Range("BR4").Select
    ActiveSheet.Paste

Could you please advise?

Thanks,

braX
  • 11,506
  • 5
  • 20
  • 33
Jimmy Jose
  • 11
  • 1
  • 1
    Your code doesn't work because `AddChart2` method appeared only in Excel 2013, and you're using Excel 2010 or older... – JohnyL May 22 '18 at 18:41

1 Answers1

-1

You can't , in the same command, create a chart then select it.

Instead, use a variable to store the chart first, then reference that:

Sub t()
Dim myCht
' Range("BO4", Range("BO4").End(xlDown)).Select ' Unnecessary line. You don't do anything with it.
With Sheets("Sheet1") ' Change the sheet name as needed
    Set myCht = .Shapes.AddChart2(227, xlLine)
    myCht.Select
    ActiveChart.SetSourceData .Range("BO1", .Range("BO4").End(xlDown))
    'ActiveChart.AutoScaling = True
    myCht.Parent.Cut
    .Range("BR4").Select
    .Paste
End With
End Sub

Note: I couldn't find a way around myCht.Select // ActiveChart.... If someone knows how to do that without using .Select, please let me know!

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • @JohnyL - Oh? I didn't know that. Then why didn't his code work? – BruceWayne May 22 '18 at 18:16
  • 2
    Older version of Excel? [This answer](https://stackoverflow.com/questions/27889579/what-does-the-number-in-the-addchart2-vba-macro-represents) claims that `.AddChart2` only is available in 2013 on. FWIW I recorded creating and selecting a chart in the same line and did not have any error... – BigBen May 22 '18 at 18:23
  • *You can't , in the same command, create a chart then select it.* `Sheet1.Shapes.AddChart2(, XlChartType.xlLineMarkers, 100, 150, 1000, 300).Select`. Doesn't this work? – JohnyL May 22 '18 at 18:34
  • 2
    His code didn't work because of Excel version. Excel 2010 doesn't have `AddChart2` method. – JohnyL May 22 '18 at 18:37
  • 1
    All, thank you very mucho gracias. It works on newer versions of excel.... So then is there a backward compatible version of "Addchart2"??? Anyways, thank you all again - i will be buying you all a round and will be drinking it for you too.... – Jimmy Jose May 29 '18 at 23:18