I keep getting an error when trying to run this code where I am trying to create a line chart

Question

Below is the code that I tried running on excel VBA, but I keep getting an error when trying this macro.

Sub Demand()
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "demand"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "demand"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "demand"
    Range("E1:E52").Select
    ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range("DATA!$E$1:$E$52")
        ActiveSheet.Shapes("Chart 4").IncrementLeft 204
    ActiveSheet.Shapes("Chart 4").IncrementTop -34
    ActiveSheet.Shapes("Chart 4").ScaleHeight 1.4722222222, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 4").ScaleWidth 1.1111111111, msoFalse, _
        msoScaleFromBottomRight
    Application.CutCopyMode = False

End Sub

ActiveSheet.Shapes("Chart 4").IncrementLeft 204 is the line that throws the error and it says runtime error. — Miguel Mondragon Hernandez, Feb 12 '20 at 18:45
as @BigBen says, need something like `set cht=ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select` where cht is a chart, then replace your hardcoded chart definitions with `cht` — Nathan_Sav, Feb 12 '20 at 18:48
And no need to `Select` either. Though @Nathan_Sav it looks like those are properties of a `Shape` and not a `Chart` so `cht` should be dimmed as `Shape` I assume. — BigBen, Feb 12 '20 at 18:48
Find out more about [avoiding select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Feb 12 '20 at 18:58

score 0 · Answer 1 · answered Feb 12 '20 at 19:05

Fixes:

Sub Demand()

    Dim co, cht As Chart

    Range("E1").Value = "demand"

    Set co = ActiveSheet.Shapes.AddChart2(227, xlLineMarkers)
    Set cht = co.Chart

    cht.SetSourceData Source:=Range("DATA!$E$1:$E$52")
    co.Left =  Range("E2").Left 'for example
    co.Top = Range("E2").Top
    co.Height = 400
    co.Width = 400

End Sub

I keep getting an error when trying to run this code where I am trying to create a line chart

1 Answers1