|
本帖最后由 bhgyuj123 于 2013-12-28 17:45 编辑
这个只能算一个表的数据,如果要改成用全部表的数据要怎改代码?
sub 总5()
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
Set d3 = CreateObject("Scripting.Dictionary")
On Error Resume Next
For j = 14 To 109
Row1 = Cells(Rows.Count, j).End(xlUp).Row
Arr1 = Range(Cells(3, j), Cells(Row1, j))
For i = 1 To UBound(Arr1)
d1(Arr1(i, 1)) = d1(Arr1(i, 1)) + 1
Next i
Arr2 = d1.Keys
Arr3 = d1.Items
For i = 0 To UBound(Arr3)
If Arr3(i) > 8 Then d2(Arr2(i)) = d2(Arr2(i)) + 1
Next i
d1.RemoveAll
Erase Arr2
Erase Arr3
Next j
Arr11 = d2.Keys
Arr12 = d2.Items
For i = 0 To UBound(Arr12)
If Arr12(i) > 3 Then d3(Arr11(i)) = d3(Arr11(i)) + 1
Next i
ARR21 = d3.Keys
ARR22 = d3.Items
Sheets("1").Range("B5000").End(xlUp).Offset(1, 0).Resize(UBound(ARR22) + 1, 1) = Application.WorksheetFunction.Transpose(ARR21)
End sub
bhgyuj123 发表于 2013-12-28 17:09
所有表汇成一个 - Sub 总5()
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- Set d3 = CreateObject("Scripting.Dictionary")
- For Each sht In Worksheets
- If sht.Name <> "1" Then
- With sht
- On Error Resume Next
- For j = 14 To 109
- Row1 = .Cells(Rows.Count, j).End(xlUp).Row
- Arr1 = .Range(Cells(3, j), .Cells(Row1, j))
- For i = 1 To UBound(Arr1)
- d1(Arr1(i, 1)) = d1(Arr1(i, 1)) + 1
- Next i
- Arr2 = d1.Keys
- Arr3 = d1.Items
- For i = 0 To UBound(Arr3)
- If Arr3(i) > 8 Then d2(Arr2(i)) = d2(Arr2(i)) + 1
- Next i
- d1.RemoveAll
- Erase Arr2
- Erase Arr3
- Next j
- Arr11 = d2.Keys
- Arr12 = d2.Items
- For i = 0 To UBound(Arr12)
- If Arr12(i) > 3 Then d3(Arr11(i)) = d3(Arr11(i)) + 1
- Next i
- End With
- End If
- Next
- ARR21 = d3.Keys
- ARR22 = d3.Items
- Set d3 = Nothing
- Set d2 = Nothing
- Set d1 = Nothing
- Sheets("1").Range("B5000").End(xlUp).Offset(1, 0).Resize(UBound(ARR22) + 1, 1) = Application.WorksheetFunction.Transpose(ARR21)
- End Sub
复制代码没附件,我这就没有测试了,你试试。
|
|