- Sub 自定义格式()
- Dim arr, re, d As Object, i&, dkey$
- arr = Range("A3:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
- Set d = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr)
- dkey = IIf(Left(arr(i, 1), 1) = "6", "SH", "SZ") & Format(arr(i, 1), "000000") & "|" & arr(i, 2)
- d(dkey) = d(dkey) + IIf(InStr(arr(i, 3), "万"), Replace(arr(i, 3), "万", "") * 10000, arr(i, 3))
- Next
- arr = d.keys
- ReDim re(1 To d.Count, 1 To 3)
- For i = 0 To UBound(arr)
- re(i + 1, 1) = Split(arr(i), "|")(0)
- re(i + 1, 2) = Split(arr(i), "|")(1)
- re(i + 1, 3) = d(arr(i))
- Next
- Range("E3").Resize(UBound(re), UBound(re, 2)) = re
- End Sub
复制代码 |