Chart source data

A

Anders J.

Hey

Can anyone help me to make a macro which will allow me to add sources by
activating a cell. I have the sources for the y axis. On the x-axis i would
like to update by clicking on a specific cell.

E.g
If i add something to a cell in column B (say B1), the source data in the
chart will automatically be updated to add content in column A (A1).

This is what I have tried, but i just get an error message saying compile
error: Expected: Then or GoTo

Sub AddSourceData()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet2'!$B$10:$G$110")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='Sheet2'!$U$11:$U$110"
ActiveChart.SeriesCollection(1).XValues = "='Sheet2'!$B$11"
If ActiveCell.Activate.Then XValues = Offset (0, -1).Range "B11"

End Sub

Thanks in advance
 
P

Patrick Molloy

that last IF is wrong - delete it, you aren't using it. syntax is very bad
and I can see what it does
 
A

Anders J.

Thanks

Do you have any suggestions on how I can change it for it to work? I tried
it without the IF and it still not working!
 
P

Patrick Molloy

this worked for me:

Sub AddSourceData()
Dim ch As Chart
Dim sr As Series

Set ch = Charts.Add

With ch
.SetSourceData Source:=Range("'Sheet2'!$B$10:$G$110")
.ChartType = xlLineMarkers
Set sr = .SeriesCollection.NewSeries
End With

With sr
.Values = Worksheets("sheet2").Range("U11:U110")
.XValues = Worksheets("sheet2").Range("B11")
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

No Data Lables 2
Floating Chart? 2
Bubble Chart 1
Help 0
How to change the row number in macro code? 1
add a chart in a Add-In 1
Excel 2007 Charting macro 1
#N/A isn't charting in VBA 2

Top