|
EXCEL新手,自己写了一段代码。
Sub a()
arr = Worksheets("基础数据页").Range("A2:E" & Range("A65536").End(xlUp).Row)
Set d = CreateObject("Scripting.Dictionary")
brr = Worksheets("基础数据页").Range("I1").Resize(UBound(arr), 5) '从空白处生成brr空数组
For i = 1 To UBound(arr)
k = arr(i, 2)
If Not d.exists(k) Then
d(k) = d.Count + 1
brr(d(k), 1) = arr(i, 1)
brr(d(k), 2) = arr(i, 2)
brr(d(k), 3) = arr(i, 3)
brr(d(k), 4) = arr(i, 4)
brr(d(k), 5) = arr(i, 5)
Else
brr(d(k), 4) = brr(d(k), 4) + arr(i, 4)
End If
Next i
Worksheets("输出结果").UsedRange.ClearContents
Worksheets("输出结果").Range("A1").Resize(d.Count, 5) = brr
甲 = Worksheets("输出结果").Range("A1:E" & Range("A65536").End(xlUp).Row)
ReDim 乙(1 To UBound(甲), 1 To UBound(甲, 2) - 1)
丙 = 乙
For j = 1 To UBound(甲)
If 甲(j, 2) > 70000000 Then
n = n + 1
乙(n, 1) = 甲(j, 1)
乙(n, 2) = 甲(j, 2)
乙(n, 3) = 甲(j, 3)
乙(n, 4) = 甲(j, 4)
乙(n, 5) = 甲(j, 5)
Else
m = m + 1
丙(m, 1) = 甲(j, 1)
丙(m, 2) = 甲(j, 2)
丙(m, 3) = 甲(j, 3)
丙(m, 4) = 甲(j, 4)
丙(m, 5) = 甲(j, 5)
End If
Next j
Worksheets("SK新").Range("A1").Resize(UBound(乙), UBound(乙, 5)).ClearContents
Worksheets("SK新").Range("A1").Resize(UBound(乙), UBound(乙, 5)) = 乙
Worksheets("VW新").Range("A1").Resize(UBound(丙), UBound(丙, 5)).ClearContents
Worksheets("VW新").Range("A1").Resize(UBound(丙), UBound(丙, 5)) = 丙
End Sub
上下两段单独运行都是没问题的,但是放在一起就显示下标越界,求大神帮助
|
|