字典功能的应用(循环)

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

 今天你菊子曰了么？

转载于:https://www.cnblogs.com/xiehui/archive/2010/05/11/2004294.html