0

I am making a chart using VBA with a dynamic end. This is cause a new row of data gets added every day. I made the following code:

'
' grafiek Macro
'

'
   Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Datum"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Cases"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Doden"
    Range("$A:$A").Select
    ActiveWindow.SmallScroll Down:=-132
    Range("$A:$A,$E:$F").Select
    Range("E1").Activate
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=Range( _
        "uitslag!$A:$A,uitslag!$E:$F")
    ActiveSheet.Shapes("Grafiek 3").IncrementLeft 249
    ActiveSheet.Shapes("Grafiek 3").IncrementTop -52.2
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Coronagegevens"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Coronagegevens"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 14).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 14).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select
End Sub

It says it can't find the shape "grafiek 3". How can I fix this?

Kind regards

Moi
  • 54
  • 1
  • 7
  • 2
    There is a quote `"` missing in the end of `"uitslag!$A:$A,uitslag!$E:$F)`. It should be `"uitslag!$A:$A,uitslag!$E:$F")`. – Pᴇʜ May 19 '20 at 10:32
  • Okay that worked yes, however now it says it can't a shape. – Moi May 19 '20 at 10:39
  • 1
    The error means a shape named `Grafiek 3` does not exist in your sheet. You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). This technique should fix your issue. – Pᴇʜ May 19 '20 at 10:45
  • 1
    This looks like slightly-edited macro-recorded code. Such code should almost never be part of a final product. The macro recorder is helpful for seeing which object methods of which objects are relevant for accomplishing your goals, but has the drawbacks of being selection-based and also of recording the default values of many parameters. For example `.TextDirection = msoTextDirectionLeftToRight` is almost certainly pointless since left-to-right is default direction. – John Coleman May 19 '20 at 10:51

0 Answers0