请查看
- Sub today() '提取前五大
- Dim arr, brr(), crr(), i&, j&, k&, cf, c&, n&
- arr = Intersect(Sheets("Sheet1").[e410].CurrentRegion, Sheets("Sheet1").[e410].CurrentRegion.Offset(0, 1))
- ReDim brr(1 To 70, 1 To UBound(arr, 2))
- For i = 1 To UBound(arr)
- For j = 1 To UBound(arr, 2)
- brr(arr(i, j), j) = brr(arr(i, j), j) & i & ","
- Next
- Next
-
- ReDim crr(1 To 10, 1 To UBound(arr, 2))
- For j = 1 To UBound(brr, 2)
- i = 70
- Do While n < 5
- If brr(i, j) <> "" Then
- If InStr(Left(brr(i, j), Len(brr(i, j)) - 1), ",") Then
- cf = Split(Left(brr(i, j), Len(brr(i, j)) - 1), ",")
- For c = 0 To UBound(cf)
- n = n + 1: crr(n, j) = cf(c) - 1
- Next
- Else: n = n + 1: crr(n, j) = Left(brr(i, j), Len(brr(i, j)) - 1) - 1
- End If
- End If
- i = i - 1
- Loop
- n = 0
- Next
- For i = 1 To UBound(crr)
- For j = 1 To UBound(crr, 2)
- If crr(i, j) <> "" Then
- Sheets("Sheet1").Cells(410 + crr(i, j), 4 + j).Interior.ColorIndex = Rnd * 6 + 2
- End If
- Next
- Next
- Sheets("Sheet1").[e422].Resize(20, j).ClearContents
- Sheets("Sheet1").[e422].Resize(10, j - 1) = crr
- End Sub
复制代码
|