☰

为什么 VBA 代码在使用 F8 时可以运行，但在使用 F5 时却不行

tag-icon • tag-icon microsoft-excel vba
为什么 VBA 代码在使用 F8 时可以运行，但在使用 F5 时却不行

VBA 代码在运行 F8 时有效，但在运行 F5 时无效。救命！

我甚至尝试了 Application.Wait Now() + TimeValue("00:00:03")，但它仍然不起作用。

它没有给出错误消息。第一个工作表运行正常，但第二个工作表显示结果为 0，而不是正确的值

有什么建议么？

以下是代码：

Sub edittrinvoice() ' Dim ws As Worksheet Dim i As Integer

For Each ws In ActiveWorkbook.Worksheets

ws.Name = ws.Cells(2, 28).Value & "-e"
ws.Cells(1, 1).Value = ws.Cells(2, 28).Value

ws.Columns("AD:AH").EntireColumn.Delete
ws.Columns("U:AB").EntireColumn.Delete
ws.Columns("R:S").EntireColumn.Delete
ws.Columns("C:P").EntireColumn.Delete

ws.Columns("A:H").ColumnWidth = 11

ws.Rows("2:17").EntireRow.Delete


ws.Pictures.Delete

ws.Cells.UnMerge
ws.Cells.Borders.LineStyle = xlLineStyleNone
ws.Cells.Font.Name = "calibri"
ws.Cells.Font.Size = 10
ws.Cells.VerticalAlignment = xlVAlignTop

ws.Columns("D:D").Insert Shift:=xlToRight
ws.Columns("D:D").Insert Shift:=xlToRight
ws.Columns("D:D").Insert Shift:=xlToRight


ws.Columns("H:H").Insert Shift:=xlToRight
ws.Columns("H:H").Insert Shift:=xlToRight
ws.Columns("H:H").Insert Shift:=xlToRight
ws.Columns("H:H").Insert Shift:=xlToRight


Application.DisplayAlerts = False


ws.Columns("C:C").TextToColumns Destination:=ws.Cells(1, 3), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

ws.Columns("G:G").TextToColumns Destination:=ws.Cells(1, 7), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

ws.Columns("L:L").TextToColumns Destination:=ws.Cells(1, 12), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

i = 1 
For i = 1 To 100 
If ws.Cells(i, 3) <> 0 Then 
ws.Cells(i, 5) = Cells(i, 3) + Cells(i, 4) / 100 
ws.Cells(i, 9) = Cells(i, 7) + Cells(i, 8) / 100 
ws.Cells(i, 14) = Cells(i, 12) + Cells(i, 13) / 100 
End If 
Next i


ws.Columns("J:M").EntireColumn.Delete
ws.Columns("F:H").EntireColumn.Delete
ws.Columns("C:D").EntireColumn.Delete

>> i tried to add wait here but it does not work so I removed it
' Application.Wait Now() + TimeValue("00:00:03")

Next

Application.DisplayAlerts = True

End Sub

答案1

问题出在你的循环中。除了循环中的数学运算之外，你尽职尽责地在任何地方都使用了父工作表引用。

你有，

For i = 1 To 100 
  If ws.Cells(i, 3) <> 0 Then 
    ws.Cells(i, 5) = Cells(i, 3) + Cells(i, 4) / 100 
    ws.Cells(i, 9) = Cells(i, 7) + Cells(i, 8) / 100 
    ws.Cells(i, 14) = Cells(i, 12) + Cells(i, 13) / 100 
  End If 
Next i

应该，

For i = 1 To 100 
  If ws.Cells(i, 3) <> 0 Then 
    ws.Cells(i, 5) = ws.Cells(i, 3) + ws.Cells(i, 4) / 100 
    ws.Cells(i, 9) = ws.Cells(i, 7) + ws.Cells(i, 8) / 100 
    ws.Cells(i, 14) = ws.Cells(i, 12) + ws.Cells(i, 13) / 100 
  End If 
Next i

您可能还想考虑一个With/End With块。

With ws
  For i = 1 To 100 
    If .Cells(i, 3) <> 0 Then 
      .Cells(i, 5) = .Cells(i, 3) + .Cells(i, 4) / 100 
      .Cells(i, 9) = .Cells(i, 7) + .Cells(i, 8) / 100 
      .Cells(i, 14) = .Cells(i, 12) + .Cells(i, 13) / 100 
    End If 
  Next i
End With

当然，该With/End With方法实际上可以应用于更大的工作表循环内的所有内容。

For Each ws In ActiveWorkbook.Worksheets
  With ws

    'all ws.Cells are now referred to as .Cells (note the . in .Cells)

  End With
Next ws

相关内容