VBA Excel: Macro-created graph with dynamic range

Question

I'm trying to create a macro where a graph is created with a dynamic range for the columns. Here is what I have so far.

Private Sub CommandButton2_Click()

With Sheets("Sheet1")

    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=.Range("D2", Cells(2, N + 3))
    ActiveChart.Location xlLocationAsObject

End With

End Sub

Right now however, I keep encountering a run time error with the statement-

"The specified dimension is not valid for the current chart type"

When I look in the code, it keeps referencing the last line.

ActiveChart.Location xlLocationAsObject

Any ideas? Thanks

FOLLOW UP

Here is the code referencing N

Dim N As Long
Private Sub CommandButton1_Click()

Dim x As Integer
Dim y As Integer
x = 0
y = 1

N = Application.InputBox(Prompt:="Enter value", Type:=1)
If N > Columns.Count Then
    N = Columns.Count
Else
    For i = 4 To 9999
        Cells(1, i).ClearContents
        Cells(3, i).ClearContents
    Next i
End If

For i = 4 To N + 3
    x = x + y
    Cells(1, i) = x
Next i

End Sub

What is N? What range of data are you planning to plot?

Ravi Yenugu
– Ravi Yenugu

2014-08-14 17:37:11 +00:00
Commented Aug 14, 2014 at 17:37 — Ravi Yenugu
– Ravi Yenugu, Commented Aug 14, 2014 at 17:37
@Thinkingcap sorry totally spaced. Modified the question

DeeWBee
– DeeWBee

2014-08-14 17:41:12 +00:00
Commented Aug 14, 2014 at 17:41 — DeeWBee
– DeeWBee, Commented Aug 14, 2014 at 17:41

Ravi Yenugu · Accepted Answer · 2014-08-14 18:24:21Z

1

xlLocationAsObject requires where parameter ,specifying where you want the chart object to be embedded or placed

Replace

ActiveChart.Location xlLocationAsObject

with

ActiveChart.Location xlLocationAsObject, Name

to embed the chart in Sheet1

See all available Options enter image description here

answered Aug 14, 2014 at 18:24

Ravi Yenugu

3,8885 gold badges43 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel: Macro-created graph with dynamic range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related