回圈索引混亂

我試圖通過為作業簿中的所有作業表創建一個填充有所述單元格參考中的值的陣列，來添加來自外部作業簿中任何十二個連續作業表的值，這些作業表位于同一單元格參考中。然后，使用 for 回圈，我將選擇十二個最近的值，并將它們添加到打開的作業簿上的一個單元格中。下面的代碼適用于此目的，但我只是試圖在打開的作業簿上一次用總和填充十二個單元格。目前，我正在填充 13 個單元格而不是 12 個單元格。我不知道為什么會這樣，但它可能與 for 回圈中的代碼或 for 回圈索引有關。

代碼：

Sub LoopValues()
    Application.ScreenUpdating = False  'prevents premature updates during calculation
    
    ' Define vars
    Dim wkbOpen As Excel.Workbook
    Dim wkbRef As Excel.Workbook
 
    Dim ws As Worksheet
    Dim wsName As String
    Dim wsExists As Boolean

    Dim counter As Integer
    Dim i As Integer
    
    ' set workbooks
    Set wkbOpen = ActiveWorkbook
    Set wkbRef = Application.Workbooks.Open("FileName.xlsx")
   
   
   ' creates a string for each sheet in FileName
        ' Formatting: (year, month, day) --> mmyy (disregards day)
        ' Start year: 2020
        ' Start month: Feb
        ' Start mmyy: 0220
    wsName = Format(DateSerial(2020, 2   counter, 1), "mmyy")
    
    ' Set Counter
    counter = 0
    
    ' condition for do while to run
    wsExists = True
    
    ' declare array for values from sheets
    Dim arr() As Variant
    
    ' create an empty array to store FileName values, and alter in Do While (re dim)
    arr = Array()
    
    Do While wsExists
        
        'create a string for each sheet
         wsName = Format(DateSerial(2020, 2   counter, 1), "mmyy")
         
        On Error Resume Next
        ' try to set the ws to the name of the current sheet in BatchEmissions
        Set ws = wkbRef.Sheets(wsName)
        
        ' if sheets(wsName) does not exists Err.Number <> 0
        If Err.Number <> 0 Then
            wsExists = False
            On Error GoTo 0
            
        Else
            
            ' resize array (increase bound by one) and ensure previous data is saved
            ReDim Preserve arr(UBound(arr)   1)
            
            ' update array to inlcude the sheet in FileName's value in AA208
            arr(UBound(arr)) = ws.Range("AA208").Value
        
        End If
            ' increments to consecutive sheet (mmyy) - (counter changes month value)
            counter = counter   1
    
    Loop
    
    ' close FileName
    wkbRef.Close False
    
    Dim newLowerBound As Integer
    newLowerBound = UBound(arr) - 12
    
    Dim temp As Integer
    
    temp = 0
    
    ' loop through the last twelve values in the array
    For i = newLowerBound To UBound(arr)
    
        
        ' set cell ref to value
        ' to sum arr(0) to arr(11) and increment by i each time
        ' we must transpose the array, and wrap the transposed array inside an index
        ' and for ROWS generate an array each time incremented by i through Evaluate(ROW(1:12) etc
        wkbOpen.Sheets("Site").Cells(99   temp, "Q").Value = Application.Sum(Application.Index(Application.Transpose(arr), Evaluate("Row(" & (1   temp) & ":" & (12   temp) & ")"), 0))

        temp = temp   1
    
    Next i
    
    Application.ScreenUpdating = True

End Sub

uj5u.com熱心網友回復：

這個：newLowerBound = UBound(arr) - 12

對此：newLowerBound = UBound(arr) - 11

標籤：擅长 vba for循环 excel-2010 vba7

上一篇：需要復制/粘貼結果所在的行

下一篇：VBA根據單元格值洗掉行