|
Sub 索引()
Dim i
Sheet1.Select
Sheet1.Range("A2:A" & [A65536].End(3).Row).AdvancedFilter 2, , Sheet2.Range("A1"), True
Sheet2.Select
For i = 1 To Sheet2.Cells(65536, 1).End(3).Row
Sheet2.Cells(i, 2) = WorksheetFunction.CountIf(Sheet1.Range("A:A"), Sheet2.Cells(i, 1))
Next
End Sub
结果总是会重复一次
是要选择sheet1的A列的最后一个非空单元格,不写就默认活动工作表sheet2的最后一行了,arr就等于 sheet2的 [a1:a2]了,改成下面的
Sub 改进索引()
Dim i, d, arr, brr
Set d = CreateObject("Scripting.Dictionary")
arr = Sheet1.Range("A2:A" & Sheet1.[A65536].End(3).Row)
For i = 1 To UBound(arr)
d(arr(i, 1)) = d(arr(i, 1)) + 1
Next
Sheet2.Range("A1").Resize(d.Count, 1) = Application.Transpose(d.keys)
Sheet2.Range("B1").Resize(d.Count, 1) = Application.Transpose(d.items)
' For i = 1 To d.Count
' Sheet2.Cells(i, 2) = d(Sheet2.Cells(i, 1))
' Next
End Sub
|
|