excel - 为什么我的单元格值在循环新工作簿时会堆积起来

问题描述

我是 VBA 新手。我一直在使用for循环将计数添加到我的摘要页面，但是当它遍历多个工作簿时，摘要页面会不断增加，因为它也会增加上一个工作簿的值。

如何指定计数器仅计数并添加到其特定的摘要页面中？

例如，第一个工作簿摘要页数为 10，下一个工作簿工作簿将是该工作簿的 10+。提前致谢！

    Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
    Dim x As Integer

    Dim wash_count As Integer    

    'Optimize Macro Speed
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual

    'Retrieve Target Folder Path From User
      Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

        With FldrPicker
          .Title = "Select A Target Folder"
          .AllowMultiSelect = False
            If .Show <> -1 Then GoTo NextCode
            myPath = .SelectedItems(1) & "\"
        End With

    'In Case of Cancel
    NextCode:
      myPath = myPath
      If myPath = "" Then GoTo ResetSettings

    'Target File Extension (must include wildcard "*")
      myExtension = "*.xls*"

    'Target Path with Ending Extention
      myFile = Dir(myPath & myExtension)

    'Loop through each Excel file in folder
      Do While myFile <> ""
        'Set variable equal to opened workbook
          Set wb = Workbooks.Open(Filename:=myPath & myFile)

        'Ensure Workbook has opened before moving on to next line of code
          DoEvents    

    For Each ws In wb.Sheets
        If ws.Name <> "Summary" Then

            For x = 5 To 74
                If ws.Cells(x, 2).Value = "Wash" And (ws.Cells(x, 4).Value = "T") Then
                wash_count = wash_count + 1    

                End If

            Next x                

        End If
    Next ws
    wb.Sheets("Summary").Range("D6") = wash_count       

    'Save and Close Workbook
          wb.Close SaveChanges:=True

        'Ensure Workbook has closed before moving on to next line of code
          DoEvents

        'Get next file name
          myFile = Dir
      Loop

    'Message Box when tasks are completed
      MsgBox "Task Complete!"

    ResetSettings:
      'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True    

    End Sub

标签： excelvbaloopsworksheet

excel - 为什么我的单元格值在循环新工作簿时会堆积起来

问题描述

解决方案

推荐阅读