VBA--对文件夹下的每张报表再次赋值(2)

Sub OpenAndClose()
Dim MyFile As String
Dim Arr(1000) As String
Dim count As Integer
MyFile = Dir("C:\Users\mikowoo\Desktop\EPSreport\second\result\" & "*.xlsx")
count = count + 1
Arr(count) = MyFile
    
Do While MyFile <> ""
    MyFile = Dir
    If MyFile = "" Then
        Exit Do
    End If
    count = count + 1
    Arr(count) = MyFile
Loop

midian_arr = Array("F11", "F12", "F13", "F16", "F18", "F21", "F22", "F27", "F28", "F30", "F31", "F32", "F35", "F42", "F45", "F48", "F56", "F58", "F59", "F60", "F61", "F64", "F66", "F71", "F74", "F75", "M13", "M14", "M16", "M17", "M18", "M19", "M22", "M23", "M24", "M28", "M29", "M31", "M33", "M42", "M43", "M44", "M46", "M47", "M48", "M60", "M61", "M62", "M64", "M65", "M74")
midian_index_arr = Array(7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 37, 39, 41, 43, 45, 47, 49, 51, 53, 55, 57, 59, 61, 63, 65, 67, 69, 71, 73, 75, 77, 79, 81, 83, 85, 87, 89, 91, 93, 95, 97, 99, 101, 103, 105, 109, 111, 113)
average_arr = Array("H11", "H12", "H13", "H16", "H18", "H21", "H22", "H27", "H28", "H30", "H31", "H32", "H35", "H42", "H45", "H48", "H56", "H58", "H59", "H60", "H61", "H64", "H66", "H71", "H74", "H75", "Q13", "Q14", "Q16", "Q17", "Q18", "Q19", "Q22", "Q23", "Q24", "Q28", "Q29", "Q31", "Q33", "Q42", "Q43", "Q44", "Q46", "Q47", "Q48", "Q60", "Q61", "Q62", "Q64", "Q65", "Q74")
For i = 1 To count
    Filename = "C:\Users\mikowoo\Desktop\EPSreport\second\result\" & Arr(i)
    Set wb_report = Workbooks.Open(Filename)
        For x = 3 To 558
            If wb_report.Sheets(3).Range("I2").Value = ThisWorkbook.Sheets(1).Cells(x, 1).Value Then
                len_array = UBound(midian_arr) - LBound(midian_arr)
                For a = 0 To len_array
                    wb_report.Sheets(3).Range(midian_arr(a)).Value = ThisWorkbook.Sheets(1).Cells(x, midian_index_arr(a)).Value '赋值中位值
                    wb_report.Sheets(3).Range(average_arr(a)).Value = ThisWorkbook.Sheets(1).Cells(x, midian_index_arr(a) - 1).Value '赋值均值
                Next a
                'Debug.Print x
            End If
        Next x
    wb_report.Close savechanges = False
Next i
    
Debug.Print i

'Debug.Print count
End Sub