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

Question

我在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 

Answer 1

我把自由清理了一下你的代码，你可能想尝试这一个（它应该在第一次工作）：

Sub UCS_Inventory_Mac1() 

Dim I As Integer 
Dim WS_Count As Integer 

WS_Count = ActiveWorkbook.Worksheets.Count + 1 '<-- please note: the sheets are always 7, why do you set them to 8? 

Dim ranges(1 To 7) '<-- this is the only not-indexable part of your code, store it in an array to make the code more readable 
ranges(1) = "$A$1:$E$4" 
ranges(2) = "$A$1:$B$11" 
ranges(3) = "$A$1:$D$11" 
ranges(4) = "$A$1:$D$11" 
ranges(5) = "$A$1:$D$11" 
ranges(6) = "$A$1:$C$11" 
ranges(7) = "$A$1:$B$11" 

For I = 1 To WS_Count 

    With Worksheets("Sheet" & I) '<-- no need to activate the sheet 

     With .Range("B2:H15") '<-- even here, no need to select 
     .NumberFormat = "General" 
     .Value = .Value '<-- this instruction does nothing 
     End With 

     .Range("B2").Select '<-- do you really need to select the range? This will just make your code flashing through sheets 
     With .Shapes.AddChart2(201, xlColumnClustered) 
      .SetSourceData Source:=Range("Sheet" & I & "!" & ranges(I)) 
      .SetElement (msoElementDataLabelOutSideEnd) 
      .SetElement (msoElementLegendBottom) 
      .SetElement (msoElementLegendTop) 
      .ChartTitle.Text = "Chart " & I 
     End With 

    End With 

Next I 

End Sub 

你会发现我的意见在代码本身，但我敢肯定，问题来自您滥用Select/Activate陈述，可能导致您错误地认定合格的范围。