Chart source data

[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

 

[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

 

[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!

 

[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