exchanging X and Y in a plot

[Bruce Bowler]

I have the following bit of code in a larger macro, that plots column B
along the X axis and C along the Y axis. I'd really like it to switch the
axes so that it's C on the X and B on the Y. Changing the actual columns
isn't really an option...

Charts.Add
With ActiveChart
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets(bcbsheet$).Range("B:C"),PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet
.Name = "some text"
.HasTitle = True
.ChartTitle.Characters.Text = "some text" .Axes(xlCategory,xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle= False
End With

Any hints on how to "fix it"?

Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | Human beings are the choices they make under
1.207.633.9600 | pressure. - Bob McKee
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[hanjohn]

I recorded a macro to see the code generated when I changed the X
values to column C and the Y values to column B under the Series tab of
the Source Data dialog and got
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!C3"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!C2"
Does this help?

 

[Jon Peltier]

Charts.Add
With ActiveChart
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets(bcbsheet).Range("B:B"), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(bcbsheet).Range("C:C")
.Name = "some text"
.HasTitle = True
.ChartTitle.Characters.Text = "some text"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Bruce Bowler]

Thank you, kind sir!

Charts.Add
With ActiveChart
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets(bcbsheet).Range("B:B"), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(bcbsheet).Range("C:C")
.Name = "some text"
.HasTitle = True
.ChartTitle.Characters.Text = "some text"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

--
+-------------------+---------------------------------------------------+
Bruce Bowler | You can never make anything foolproof since fools
1.207.633.9600 | are so clever. - Anonymous
(e-mail address removed) |
+-------------------+---------------------------------------------------+