|
发表于 2012-1-5 21:52
|
显示全部楼层
本楼为最佳答案
- Private arr2
- Sub aa()
- Dim arr1, arr3()
- Dim i As Long, j As Long
- Set d = CreateObject("Scripting.Dictionary")
- arr1 = Range("C5:H11") '蓝色区域赋值给数组
- arr2 = Range("B13:G17") '红色区域赋值给数组
- ReDim arr3(1 To UBound(arr2, 1), 1 To UBound(arr2, 2))
- For i = 1 To UBound(arr1, 1) '在蓝色区域数组行循环
- For j = 1 To UBound(arr1, 2) '在蓝色区域数组列循环
- If arr1(i, j) <> "" Then
- d(arr1(i, j)) = arr1(i + 1, j)
- arr1(i + 1, j) = ""
- End If
- Next j
- Next i
- For i = 1 To UBound(arr2, 1) '在红色区域数组行循环
- For j = 1 To UBound(arr2, 2) '在红色区域数组列循环
- If arr2(i, j) <> "" Then
- arr3(i, j) = arr2(i, j)
- arr3(i + 1, j) = d(arr2(i, j))
- End If
- Next j
- Next i
- Range("B13").Resize(UBound(arr3, 1), UBound(arr3, 2)) = arr3
- End Sub
- Sub qk()
- Range("B13").Resize(UBound(arr2, 1), UBound(arr2, 2)) = arr2
- End Sub
复制代码 注意:复制之后可以清空,但是不能连续点击两次复制,否则程序出错,而且无法完成清空
附件:
不连续的单元格赋值给数组-sunjing.rar
(12.96 KB, 下载次数: 87)
|
|