我必须运行宏两次才能使其正确

2023-05-27 16:07| 来源: 网络整理| 查看: 265

我在Excel中创建了一个宏，它遍历每个工作表-将文本转换为数字，然后根据数据创建图形。我需要运行我的宏两次才能工作。

第一次，只有第一张纸的图形是正确的。对于其他选项卡，所有文本都转换为数字，但图形没有映射到正确的数据。

第二次运行宏时，一切都是正常的。

Sub UCS_Inventory_Mac1() Dim I As Integer Dim WS_Count As Integer WS_Count = ActiveWorkbook.Worksheets.Count + 1 For I = 1 To WS_Count Range("B2:H15").Select With Selection Selection.NumberFormat = "General" .Value = .Value End With If I = 1 Then Range("B2").Select ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$E$4") ActiveChart.SetElement (msoElementDataLabelOutSideEnd) ActiveChart.SetElement (msoElementLegendBottom) ActiveChart.SetElement (msoElementLegendTop) ActiveChart.ChartTitle.Text = "Chart 1" ElseIf I = 2 Then Worksheets("Sheet2").Activate Range("B2").Select ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$B$11") ActiveChart.SetElement (msoElementDataLabelOutSideEnd) ActiveChart.SetElement (msoElementLegendBottom) ActiveChart.SetElement (msoElementLegendTop) ActiveChart.ChartTitle.Text = "Chart 2" ElseIf I = 3 Then Worksheets("Sheet3").Activate Range("B2").Select ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select ActiveChart.SetSourceData Source:=Range("Sheet3!$A$1:$D$11") ActiveChart.SetElement (msoElementDataLabelOutSideEnd) ActiveChart.SetElement (msoElementLegendBottom) ActiveChart.SetElement (msoElementLegendTop) ActiveChart.ChartTitle.Text = "Chart 3" ElseIf I = 4 Then Worksheets("Sheet4").Activate Range("B2").Select ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select ActiveChart.SetSourceData Source:=Range("Sheet4!$A$1:$D$11") ActiveChart.SetElement (msoElementDataLabelOutSideEnd) ActiveChart.SetElement (msoElementLegendBottom) ActiveChart.SetElement (msoElementLegendTop) ActiveChart.ChartTitle.Text = "Chart 4" ElseIf I = 5 Then Worksheets("Sheet5").Activate Range("B2").Select ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select ActiveChart.SetSourceData Source:=Range("Sheet5!$A$1:$D$11") ActiveChart.SetElement (msoElementDataLabelOutSideEnd) ActiveChart.SetElement (msoElementLegendBottom) ActiveChart.SetElement (msoElementLegendTop) ActiveChart.ChartTitle.Text = "Chart 5" ElseIf I = 6 Then Worksheets("Sheet6").Activate Range("B2").Select ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select ActiveChart.SetSourceData Source:=Range("Sheet6!$A$1:$C$11") ActiveChart.SetElement (msoElementDataLabelOutSideEnd) ActiveChart.SetElement (msoElementLegendBottom) ActiveChart.SetElement (msoElementLegendTop) ActiveChart.ChartTitle.Text = "Chart 6" ElseIf I = 7 Then Worksheets("Sheet7").Activate Range("B2").Select ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select ActiveChart.SetSourceData Source:=Range("Sheet7!$A$1:$B$11") ActiveChart.SetElement (msoElementDataLabelOutSideEnd) ActiveChart.SetElement (msoElementLegendBottom) ActiveChart.SetElement (msoElementLegendTop) ActiveChart.ChartTitle.Text = "Chart 7" End If Next I End Sub

【本文地址】

公司简介

联系我们