时间戳

首页 > 解决方案 > Excel VBA - 循环未正确添加数据

excel - Excel VBA - 循环未正确添加数据

问题描述

我有下面的代码，允许我打开 12 个 Excel 数据文件，一年中的每个月一个。在每个文件上运行宏以清理数据后，主代码应该从特定列/行中获取数据，将其编译在一起，然后将其输入到新工作簿中。

我能够循环打开每个文件并在它们上运行自定义宏，但无法将它们添加在一起。

以下是我拥有的完整代码。在“将数据添加到表格”部分下，尝试使用正确的代码，但它似乎只是将每个单元格的最后几个月数据添加到新书中，而不是从每个单元格添加 12 个月的数据。接下来的 4 节是我使用的旧代码，第 2 节和第 4 节除以 12 得到平均值（这些是以分钟/秒为单位的调用时间，其中第 1、第 3 和第 5 节是总数）。

Sub ECHI12MonthCombiner()
'
' ECHI12MonthCombiner Macro
'
'
'Intialize workbooks
    Dim MonthSum(1 To 12) As Workbook
    Dim File As Variant
    ChDir "C:\Users\xxxxxxxxxxxxxxx\Desktop\Monthly Performance Summary"
    Dim Month As Integer
    For Month = 1 To 12
        File = Application.GetOpenFilename
        Set MonthSum(Month) = Workbooks.Open(File)
        ActiveSheet.name = MonthName(Month, True)
        Call ECHIBasicMonthlySummary
    Next

'Create new workbook
    Dim combinedMonthlySummaries As Workbook
    Set combinedMonthlySummaries = Workbooks.Add
    Sheets("Sheet1").name = "Call Data"

'Change analysts to variables
    Set Anal = Workbooks.Open("C:\Users\stefan.bagnato\Desktop\Analysts\Analysts")
    Dim var1, var2, var3, var4, var5, var6, var7 As String
    var1 = Workbooks("Analysts").Worksheets("Analysts").Range("A1")
    var2 = Workbooks("Analysts").Worksheets("Analysts").Range("A2")
    var3 = Workbooks("Analysts").Worksheets("Analysts").Range("A3")
    var4 = Workbooks("Analysts").Worksheets("Analysts").Range("A4")
    var5 = Workbooks("Analysts").Worksheets("Analysts").Range("A5")
    var6 = Workbooks("Analysts").Worksheets("Analysts").Range("A6")
    var7 = Workbooks("Analysts").Worksheets("Analysts").Range("A7")
    Workbooks("Analysts").Close

'Create table
    'Add table headers
        Range("A2") = var1
        Range("A3") = var2
        Range("A4") = var3
        Range("A5") = var4
        Range("A6") = var5
        Range("A7") = var6
        Range("A8") = var7
        Range("B1") = "Staff Time"
        Range("C1") = "Calls Offered"
        Range("D1") = "ACD Calls"
        Range("E1") = "AHT"
        Range("F1") = "Exn Out Calls"
        Range("G1") = "Avg Extn Out Time"
        Range("H1") = "Avail Time"
        Range("I1") = "AUX Time"
        Range("J1") = "Lunch Break"
        Range("K1") = "Short Break"
        Range("L1") = "Special Project"
        Range("M1") = "In A Meeting"
        Range("N1") = "Default"
        Range("O1") = "Tea Break"
    'Justify cells
        Range("B1:O8").HorizontalAlignment = xlCenter
    'Format columns
        Range("B2:B8,E2:E8,G2:O8").NumberFormat = "[h]:mm:ss"
        Range("A2:A8,B1:O1").Font.Bold = True
    'Widen columns
        Range("A:A").ColumnWidth = 16.5
        Range("B:O").ColumnWidth = 12
    'Wrap text
        Range("B1:O1").WrapText = True

'Add data to table
    Dim callRow As Long, callCol As Long, Sum As Double
    For callRow = 2 To 8
        For callCol = 2 To 4
            For Month = 1 To 12
                Sum = MonthSum(Month).Worksheets(MonthName(Month, True)).Cells(callRow, callCol).Value
            Next
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = Sum
        Next
    Next
    For callRow = 2 To 8
        For callCol = 5 To 5
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = _
                (monthSumJan.Worksheets("Agent Group Summary Monthly-jan").Cells(callRow, callCol).Value + _
                monthSumFeb.Worksheets("Agent Group Summary Monthly-feb").Cells(callRow, callCol).Value + _
                monthSumMar.Worksheets("Agent Group Summary Monthly-mar").Cells(callRow, callCol).Value + _
                monthSumApr.Worksheets("Agent Group Summary Monthly-apr").Cells(callRow, callCol).Value + _
                monthSumMay.Worksheets("Agent Group Summary Monthly-may").Cells(callRow, callCol).Value + _
                monthSumJun.Worksheets("Agent Group Summary Monthly-jun").Cells(callRow, callCol).Value + _
                monthSumJul.Worksheets("Agent Group Summary Monthly-jul").Cells(callRow, callCol).Value + _
                monthSumAug.Worksheets("Agent Group Summary Monthly-aug").Cells(callRow, callCol).Value + _
                monthSumSep.Worksheets("Agent Group Summary Monthly-sep").Cells(callRow, callCol).Value + _
                monthSumOct.Worksheets("Agent Group Summary Monthly-oct").Cells(callRow, callCol).Value + _
                monthSumNov.Worksheets("Agent Group Summary Monthly-nov").Cells(callRow, callCol).Value + _
                monthSumDec.Worksheets("Agent Group Summary Monthly-dec").Cells(callRow, callCol).Value) / 12
        Next
    Next
    For callRow = 2 To 8
        For callCol = 6 To 6
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = _
                monthSumJan.Worksheets("Agent Group Summary Monthly-jan").Cells(callRow, callCol).Value + _
                monthSumFeb.Worksheets("Agent Group Summary Monthly-feb").Cells(callRow, callCol).Value + _
                monthSumMar.Worksheets("Agent Group Summary Monthly-mar").Cells(callRow, callCol).Value + _
                monthSumApr.Worksheets("Agent Group Summary Monthly-apr").Cells(callRow, callCol).Value + _
                monthSumMay.Worksheets("Agent Group Summary Monthly-may").Cells(callRow, callCol).Value + _
                monthSumJun.Worksheets("Agent Group Summary Monthly-jun").Cells(callRow, callCol).Value + _
                monthSumJul.Worksheets("Agent Group Summary Monthly-jul").Cells(callRow, callCol).Value + _
                monthSumAug.Worksheets("Agent Group Summary Monthly-aug").Cells(callRow, callCol).Value + _
                monthSumSep.Worksheets("Agent Group Summary Monthly-sep").Cells(callRow, callCol).Value + _
                monthSumOct.Worksheets("Agent Group Summary Monthly-oct").Cells(callRow, callCol).Value + _
                monthSumNov.Worksheets("Agent Group Summary Monthly-nov").Cells(callRow, callCol).Value + _
                monthSumDec.Worksheets("Agent Group Summary Monthly-dec").Cells(callRow, callCol).Value
        Next
    Next
    For callRow = 2 To 8
        For callCol = 7 To 7
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = _
                (monthSumJan.Worksheets("Agent Group Summary Monthly-jan").Cells(callRow, callCol).Value + _
                monthSumFeb.Worksheets("Agent Group Summary Monthly-feb").Cells(callRow, callCol).Value + _
                monthSumMar.Worksheets("Agent Group Summary Monthly-mar").Cells(callRow, callCol).Value + _
                monthSumApr.Worksheets("Agent Group Summary Monthly-apr").Cells(callRow, callCol).Value + _
                monthSumMay.Worksheets("Agent Group Summary Monthly-may").Cells(callRow, callCol).Value + _
                monthSumJun.Worksheets("Agent Group Summary Monthly-jun").Cells(callRow, callCol).Value + _
                monthSumJul.Worksheets("Agent Group Summary Monthly-jul").Cells(callRow, callCol).Value + _
                monthSumAug.Worksheets("Agent Group Summary Monthly-aug").Cells(callRow, callCol).Value + _
                monthSumSep.Worksheets("Agent Group Summary Monthly-sep").Cells(callRow, callCol).Value + _
                monthSumOct.Worksheets("Agent Group Summary Monthly-oct").Cells(callRow, callCol).Value + _
                monthSumNov.Worksheets("Agent Group Summary Monthly-nov").Cells(callRow, callCol).Value + _
                monthSumDec.Worksheets("Agent Group Summary Monthly-dec").Cells(callRow, callCol).Value) / 12
        Next
    Next
    For callRow = 2 To 8
        For callCol = 8 To 15
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = _
                monthSumJan.Worksheets("Agent Group Summary Monthly-jan").Cells(callRow, callCol).Value + _
                monthSumFeb.Worksheets("Agent Group Summary Monthly-feb").Cells(callRow, callCol).Value + _
                monthSumMar.Worksheets("Agent Group Summary Monthly-mar").Cells(callRow, callCol).Value + _
                monthSumApr.Worksheets("Agent Group Summary Monthly-apr").Cells(callRow, callCol).Value + _
                monthSumMay.Worksheets("Agent Group Summary Monthly-may").Cells(callRow, callCol).Value + _
                monthSumJun.Worksheets("Agent Group Summary Monthly-jun").Cells(callRow, callCol).Value + _
                monthSumJul.Worksheets("Agent Group Summary Monthly-jul").Cells(callRow, callCol).Value + _
                monthSumAug.Worksheets("Agent Group Summary Monthly-aug").Cells(callRow, callCol).Value + _
                monthSumSep.Worksheets("Agent Group Summary Monthly-sep").Cells(callRow, callCol).Value + _
                monthSumOct.Worksheets("Agent Group Summary Monthly-oct").Cells(callRow, callCol).Value + _
                monthSumNov.Worksheets("Agent Group Summary Monthly-nov").Cells(callRow, callCol).Value + _
                monthSumDec.Worksheets("Agent Group Summary Monthly-dec").Cells(callRow, callCol).Value
        Next
    Next

'Close monthly summary workbooks
    Application.DisplayAlerts = False
        For Month = 1 To 12
            MonthSum(Month).Close
        Next
    Application.DisplayAlerts = True

编辑-响应@Spinjector 添加Sum +到`Sum = MonthSum(Month).Worksheets(MonthName(Month, True)).Cells(callRow, callCol).Value'时，我得到以下值，这些值是随机且不正确的。 在此处输入图像描述

标签： excelvba

解决方案

似乎缺少总和的加法和除法。

见下文。注意我添加的位：Sum = Sum + ...和= Sum / 12.

Dim callRow As Long
Dim callCol As Long
Dim Sum As Double
For callRow = 2 To 8
    For callCol = 2 To 4
        Sum = 0 'Reset the sum for each pass of the loop.
        For Month = 1 To 12
            Sum = Sum + MonthSum(Month).Worksheets(MonthName(Month, True)).Cells(callRow, callCol).Value
        Next
        combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = Sum / 12
    Next
Next

推荐阅读