|
本帖最后由 edisonx32123 于 2017-8-15 16:21 编辑
如上图所示,需要在J列统计出文具在单元格区域出现的次数,请教代码该如何写。以下是我写的代码:
Sub tj()
Dim d As New Dictionary
Dim x As Integer, y As Integer
Dim arr
arr = Range("a2:g" & Range("a65536").End(xlUp).Row)
For x = 1 To UBound(arr)
For y = 1 To UBound(arr, 2)
If arr(x, y) <> "" Then
d(arr(x, y)) = ""
End If
Next y
Next x
Range("i2").Resize(d.Count, 1) = Application.WorksheetFunction.Transpose(d.Keys)
Range("j2") = Application.WorksheetFunction.CountIf(Range("a2:g16"), Range("i2"))
Range("j2:j16").FillDown '这句应该是不对的
End Sub
Sub tj()
Dim d As New Dictionary
Dim x As Integer, y As Integer
Dim arr
arr = Range("a2:g" & Range("a65536").End(xlUp).Row)
For x = 1 To UBound(arr)
For y = 1 To UBound(arr, 2)
If arr(x, y) <> "" Then
d(arr(x, y)) = ""
End If
Next y
Next x
Range("i2").Resize(d.Count, 1) = Application.WorksheetFunction.Transpose(d.Keys)
Range("j2") = Application.WorksheetFunction.CountIf(Range("a2:g16"), Range("i2"))
Range("j2:j16").FillDown '这句应该是不对的
End Sub
======================================
例如:
Range("j2") .Formula = "=c12"
Range("j2:j16")..FillDown
这样才是填充公式. 你那样是填充的一个常数
用vba 往单元格 写 公式 , 公式里面有 " 就比较绕了 , 不好处理
===========================================
上面直接改为 就好
d(arr(x, y)) = d(arr(x, y))+1
Range("j2").Resize(d.Count, 1) = Application.WorksheetFunction.Transpose(d.items)
|
|