|
喜欢电子表格 发表于 2012-9-9 22:19
真是不好意思,还要麻烦你:在abcdef6列中每列的数字如果换成其他自然数,这个做好的vba就不能用,能不 ... - Option Explicit
- Private Sub ColorMark_Click()
- Dim i, j, k, l, dL, dS, arr(1 To 6), arr1(1 To 6), temp, temp1, m, n, p
- Set dL = CreateObject("scripting.dictionary")
- Set dS = CreateObject("scripting.dictionary")
- Cells.Interior.ColorIndex = 0
- For i = 8 To Application.InputBox("请选择待处理数据区域,从第一行开始到待分析数据末尾", "数据源", , , , , , 8).Rows.Count
- For k = i - 1 To 1 Step -1
- m = 0
- For l = 1 To 6
- arr(l) = Application.WorksheetFunction.Count(Range(Cells(k, l), Cells(i - 1, l)))
- arr1(l) = Cells(65536, l).End(xlUp) '这里是用来提取每一列中的数字的
- Next l
- For p = 1 To 6 '在这里设置你要停止统计开始分组的条件
- If arr(p) >= 2 And arr(p) > Application.WorksheetFunction.Small(arr, 3) Then m = m + 1
- Next p
- If m = 3 Then
- For n = 1 To 3
- For j = 6 To n + 1 Step -1
- If arr(j) > arr(j - 1) Then
- temp = arr(j)
- temp1 = arr1(j)
- arr(j) = arr(j - 1)
- arr1(j) = arr1(j - 1)
- arr(j - 1) = temp
- arr1(j - 1) = temp1
- End If
- Next j
- Next n
- Exit For
- End If
- Next k
- For j = 1 To 6
- If j < 4 Then
- dL(arr1(j)) = arr1(j)
- Else
- dS(arr1(j)) = arr1(j)
- End If
- Next j
- For j = 1 To 6
- If dL.Exists(Cells(i, j).Value) Then Cells(i, j).Interior.ColorIndex = 3
- If dS.Exists(Cells(i, j).Value) Then Cells(i, j).Interior.ColorIndex = 5
- Next j
- dL.RemoveAll: dS.RemoveAll
- Erase arr: Erase arr1
- Next i
- End Sub
复制代码
用vba进行统计.zip
(21.05 KB, 下载次数: 2, 售价: 1 个金币)
|
|