使用excel VBA更改图表和图例的方向 [英] Change orientation of chart and legend using excel VBA

问题描述

由于某些原因，如果有少于四行的数据，并尝试和散点图，集群（A列）显示在图例中，但如果存在超过4行的数据，则其他列（A， B，C）在图例（Y轴）上。

你能告诉我使用VBA如何重定向这个图表看起来像图 2）？

我需要使用VBA将图例系列重新定位在水平类别轴上（因为宏记录器不显示
com / KV3fV.pngalt =enter image description here>

图2这是我如何做手动

（重新导向图例条目 - 左侧和类别标签 - 右）

GRAPH 3散点图对话框看起来像这样，这是我想要的在VBA中执行

GRAPH 4 Final Graph看起来像这样，这是我想在VBA中实现的

最后，这是宏录制器创建的，但是当我尝试运行它时，缺少，范围和Plotby参数并失败

 范围（A1：D3）。 b Range（D3）。激活
 ActiveSheet.Shapes.AddChart.Select 
 ActiveChart.ChartType = xlXYScatter 
 ActiveChart.SetSourceData 
  

解决方案

您的列和行相反。当你选择图表时，去设计上下文选项卡，然后单击切换行/列。这是解决它吗？如果是，您可以使用：

  ActiveChart.PlotBy = xlRows或ActiveChart.PlotBy = xlColumns 
  

以编程方式。

For some reason if there is less than four rows of data and you try and scatter chart it the cluster (column A) is shown on the legend, but if more than 4 rows of data exist then the other columns (A, B, C) are on the legend (Y axis).

Can you please show me using VBA how do I re orientate this graph to look like graph (2)?

I need to reorient the legend series to be on the horizontal category axis using VBA(since the macro recorder doesn't show how to do this.

GRAPH 1 STARTS OFF LOOKING LIKE THIS

GRAPH 2 THIS IS HOW I DO IT MANUALLY

(reorient legend entries - left, and category labels - right)

GRAPH 3 The scatterchart dialog ends up looking like this, which is what I want to do in VBA

GRAPH 4 Final Graph looks like this, This is what I want to achieve in VBA

Lastly this is what the macro recorder creates, but when I try and run it, its missing, the range and Plotby parameters and fails

Range("A1:D3").Select
Range("D3").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData

解决方案

Your columns and rows are reversed. When you have the chart selected go und "Design" contextual tab and click "Switch Row/Column". Does that fix it? If yes you can use:

 ActiveChart.PlotBy = xlRows or  ActiveChart.PlotBy = xlColumns

to do it programatically.

这篇关于使用excel VBA更改图表和图例的方向的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！