[已解决]怎么忽略空值

张雄友

怎么忽略空值?

最佳答案

月排行榜 / 总排行榜

1032446692

2013-6-7 20:30

张雄友 发表于 2013-6-7 20:12
无法实现。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ar, i%, d As Object
Application.EnableEvents = False
If Target.Column = 1 Then
    Set d = CreateObject("scripting.dictionary")
    [b2].Resize(Cells(Rows.Count, 1).End(3).Row - 1, 2).ClearContents
    ar = Range("a2:C" & Cells(Rows.Count, 1).End(3).Row)
    For i = 1 To UBound(ar)
        If Not d.exists(ar(i, 1)) Then ar(i, 2) = ar(i, 1)
        d(ar(i, 1)) = d(ar(i, 1)) + 1
        ar(i, 3) = d(ar(i, 1))
    Next
    [a2].Resize(UBound(ar), 3) = ar
    Set d = Nothing
End If
Application.EnableEvents = True
End Sub

跳转到最佳答案楼层

hoogle

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ar, i%, d As Object
If Target.Column = 1 Then
    Set d = CreateObject("scripting.dictionary")
    ar = Range("a2:a" & Cells(Rows.Count, 1).End(3).Row)
    For i = 1 To UBound(ar)
        If ar(i, 1) <> "" Then
            d(ar(i, 1)) = d(ar(i, 1)) + 1
        End If
    Next
    [b2].Resize(UBound(ar), 2).ClearContents
    [b2].Resize(d.Count) = Application.Transpose(d.keys)
    [c2].Resize(d.Count) = Application.Transpose(d.items)
    Set d = Nothing
End If
End Sub

1032446692

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ar, i%, d As Object
If Target.Column = 1 Then
    Set d = CreateObject("scripting.dictionary")
    ar = Range("a2:C" & Cells(Rows.Count, 1).End(3).Row - 1)
    For i = 1 To UBound(ar)
        If Not d.exists(ar(i, 1)) Then ar(i, 2) = ar(i, 1)
        d(ar(i, 1)) = d(ar(i, 1)) + 1
    Next
    [a2].Resize(UBound(ar), 3) = ar
    Set d = Nothing
End If
End Sub

lyf7276

帮你改好了，你现在试下看看 怎么忽略空值.rar (12.33 KB, 下载次数: 13)

2013-6-7 20:06 上传

点击文件名下载附件

鱼鱼简澜

这  

鱼鱼简澜

是               

鱼鱼简澜

什么啊？？？？？？？？？

鱼鱼简澜

老长老长的。。。。。。

鱼鱼简澜

看都看不懂啊。。。。。

张雄友

1032446692 发表于 2013-6-7 20:05
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ar, i%, d As Object
If Target.Column = 1 T ...

无法实现。