|
发表于 2017-8-23 17:33
|
显示全部楼层
本楼为最佳答案
- Public Sub update()
- Dim arr(), brr(), crr(), d, i, k, n
- Set d = CreateObject("scripting.dictionary")
-
- arr = Sheet1.Range("a2:ar" & Sheet1.Range("a65536").End(3).Row) '有多少列就修改这行的ar所对应的那个最后列名,最后一列比如是as就ar修改成as,
- brr = Sheet2.Range("a2:ar" & Sheet2.Range("a65536").End(3).Row) '同上
-
- ReDim crr(1 To UBound(brr, 2) - 1)
-
- For i = 1 To UBound(brr, 1) 'sheet2写入字典
-
- For n = 1 To (UBound(brr, 2) - 1) '装入字典的item数组
- crr(n) = brr(i, n + 1)
- Next n
-
- d(brr(i, 1)) = crr '写入字典
- ReDim crr(1 To UBound(brr, 2) - 1) '清空数组
- Next i
-
- For k = 1 To UBound(arr, 1) '更新数组
- If d.exists(arr(k, 1)) Then
- For n = 1 To (UBound(brr, 2) - 1)
- If Not VBA.IsEmpty(d(arr(k, 1))(n)) Then ' 添加判断,空值不写入数组
- arr(k, n + 1) = d(arr(k, 1))(n)
- End If
- Next n
- End If
- Next k
- Sheet1.[a2].Resize(UBound(arr, 1), UBound(arr, 2)) = arr '写入
- End Sub
复制代码 应该可以了
|
|