字典功能的應用(迴圈)

Sub 字典功能應用()
    Dim i As Integer, arr()
    Dim dic As Object, wb As Object
    Dim kl As Integer
    For kl = 1 To 2
        Set wb = GetObject(ThisWorkbook.Path & "\彙總資料調整格式版.xls") ‘資料來源
        With wb.Sheets("sheet1")
            i = .Range("A65536").End(xlUp).Row
            arr = .Range("A1").Resize(i, 2).Value
        End With
        wb.Close False
        Set dic = CreateObject("scripting.dictionary") ‘ 建立一個字典
        For i = 2 To UBound(arr) ‘取得arr陣列的上限,從2開始,1為標題行,所以不用再取得.
            dic(arr(i, 1)) = arr(i, 2)
        Next i
        Erase arr
        With ThisWorkbook.Sheets(kl)
            i = .Range("A65536").End(xlUp).Row
            arr = Range("A1").Resize(i, 2).Value
            For i = 2 To UBound(arr)
                arr(i, 2) = dic(arr(i, 1))
            Next i
           ‘要求貼上範圍與arr陣列範圍必須一致!!

            i = .Range("A65536").End(xlUp).Row
            .Range("A1").Resize(.Range("A65536").End(3).Row, 2).NumberFormatLocal = "@"
            .Range("A1").Resize(i, 2).Value = arr
        End With
    Next kl
End Sub

 今天你菊子曰了麼？