关于通过代码的条形图的2个问题 [英] 2 Questions about Bar Charts via Code

问题描述

我正在使用Excel 2010。

这两个问题很简单，但我的时间很短。我必须迅速扭转局面。抱歉。

我的2个问题是。 。 。

1。）I用户希望条形图位于F列的第一行。如何将图表移动到该位置。

2。）此时Y轴显示25％，50％100％。我需要Y轴显示100万200万美元等等。它应该像数据中的数字。今天的数字是数百万，所以图表应该是相同的。如果某天数字低于1
百万，那么Y轴也应如此。

这是我的代码。

Sub Chart_It（）



Dim lastrow As Integer

Dim nRow As Long

Dim strWBName As String

Dim strWBTemplateName As String

strWBTemplateName = ActiveWorkbook.Name

范围（"A1"）。选择

Selection.CurrentRegion.Select

Selection.Copy

Workbooks.Add

strWBName = ActiveWorkbook.Name

ActiveSheet.Paste

列（" B：B"）。EntireColumn.AutoFit

列（"C：C"）。EntireColumn.AutoFit



范围（"A1"）。选择

Application.DisplayFullScreen = True

lastrow = ActiveSheet.UsedRange.Rows.Count

'通过第一列并更改月份名称

nRow = 3

Do while nRow< = lastrow

 单元格（nRow，1）.Value = MonthName（Cells（nRow，1）.Value）

  nRow = nRow + 1

循环

'创建堆积图

范围（ "A1"）。选择

Selection.CurrentRegion.Select

ActiveSheet.Shapes.AddChart2（297，xlColumnStacked100）。选择

ActiveChart。 SetSourceData Source：= Range（"A1：C"& lastrow）



Windows（strWBTemplateName）。激活

ActiveWorkbook.Close savechanges ：= False

 



End Sub

解决方案

第一个问题请尝试以下方法

     ; Dim shp As Shape

    Dim lastRow As Long

    lastRow = ActiveSheet.UsedRange.Rows.Count   '用于测试<
   

   范围（"A1"）。选择

    Selection.CurrentRegion.Select

   设置shp = ActiveSheet.Shapes.AddChart2（297，xlColumnStacked100）   '删除。从行尾选择

  &NBSP;&NBSP;使用shp

        。选择

        .Top =范围（"F1"）。顶部

        .Left =范围（"F1"）。左为
   结束

    ActiveChart.SetSourceData来源：=范围（"A1：C"& lastRow）

对于第二个问题，请查看以下链接是否会帮助。

https://excelribbon.tips.net/T007888_Numeric_Value_and_Percentage_Value_in_a_Graph_Column.html

I am using Excel 2010.

These 2 question are easy but I am very short on time. I have to turn this around quickly. Sorry.

My 2 questions are . . .

1.) I The user wants the bar chart to be on the first row in Column F. How do I move the chart to that location.

2.) At the moment the Y axis shows 25%, 50% 100%. I need the Y axis to show money 1 million 2 million and so. It should be like the numbers in the data. Today the numbers is in millions so the chart should be the same. If some day the numbers is below 1 million so should the Y axis.

Here is my code.

Sub Chart_It()

Dim lastrow As Integer
Dim nRow As Long
Dim strWBName As String
Dim strWBTemplateName As String

strWBTemplateName = ActiveWorkbook.Name
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Add

strWBName = ActiveWorkbook.Name
ActiveSheet.Paste
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit

Range("A1").Select
Application.DisplayFullScreen = True
lastrow = ActiveSheet.UsedRange.Rows.Count

'Go thru first column and change number of month to month name

nRow = 3
Do While nRow <= lastrow
  Cells(nRow, 1).Value = MonthName(Cells(nRow, 1).Value)
  nRow = nRow + 1
Loop

'Create Stacked Chart

Range("A1").Select
Selection.CurrentRegion.Select
ActiveSheet.Shapes.AddChart2(297, xlColumnStacked100).Select
ActiveChart.SetSourceData Source:=Range("A1:C" & lastrow)

Windows(strWBTemplateName).Activate
ActiveWorkbook.Close savechanges:=False
 

End Sub

解决方案

Try the following for the first question

    Dim shp As Shape
    Dim lastRow As Long

    lastRow = ActiveSheet.UsedRange.Rows.Count   'Used here for testing
   
    Range("A1").Select
    Selection.CurrentRegion.Select
    Set shp = ActiveSheet.Shapes.AddChart2(297, xlColumnStacked100)  'Remove .Select from end of your line
    With shp
        .Select
        .Top = Range("F1").Top
        .Left = Range("F1").Left
    End With

    ActiveChart.SetSourceData Source:=Range("A1:C" & lastRow)

For the second question see if the following link will help.

https://excelribbon.tips.net/T007888_Numeric_Value_and_Percentage_Value_in_a_Graph_Column.html

这篇关于关于通过代码的条形图的2个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！