怎样屏蔽C列出现的#N/A为空白？

WYS67

老师：如图所示，怎样屏蔽--当再没有不重复数据可以提取时，C列下面出现的#N/A为空白？代码如下：
Function BCFSJ(Index As Integer, ParamArray arglist() As Variant)
Dim NotRepeat As Object, tStr As String
Set NotRepeat = CreateObject("Scripting.Dictionary")
For Each arg In arglist
For Each rRan In arg
If TypeName(rRan) = "Range" Then
If rRan.Value <> "" Then NotRepeat(rRan.Value) = 0
Else
NotRepeat(rRan) = 0
End If
Next
Next
If Index < 1 Then
BCFSJ =Application.TRANSPOSE（NotRepeat.keys）
ElseIf Index <= NotRepeat.Count Then
arr = NotRepeat.keys
BCFSJ = arr(Index - 1)
Else
BCFSJ = ""
End If
End Function

恳请老师们在合适的地方添加：屏蔽#N/A错误为空白的代码。

提取不重复数据的自定义函数.zip (15.02 KB, 下载次数: 0)

⊙⊙小噤○

公式本身就可以

WYS67

⊙⊙小噤○ 发表于 2019-4-1 07:35
公式本身就可以

老师：我想在代码里实现。

WYS67

怎样在代码里屏蔽#N/A错误为空白？

mzbao

你后面的错误#N/A是因为数组大小不足导致的，我在你代码的基础上，稍微修改了一点。
Function BCFSJ(Index As Integer, ParamArray arglist() As Variant)
    Dim NotRepeat As Object, tStr As String, k%, rArr() As String
    Set NotRepeat = CreateObject("Scripting.Dictionary")
    For Each arg In arglist
        For Each rRan In arg
            If TypeName(rRan) = "Range" Then
                If rRan.Value <> "" Then NotRepeat(rRan.Value) = 0
            Else
                NotRepeat(rRan) = 0
            End If
            k = k + 1
        Next
    Next
    arr = NotRepeat.keys
    ReDim rArr(0 To k)
    If Index < 1 Then
        For i% = 0 To UBound(arr)
            rArr(i) = arr(i)
        Next
        BCFSJ = Application.Transpose(rArr)
    ElseIf Index <= NotRepeat.Count Then
        BCFSJ = arr(Index - 1)
    Else
        BCFSJ = ""
    End If
End Function

WYS67

mzbao 发表于 2019-4-1 10:03
你后面的错误#N/A是因为数组大小不足导致的，我在你代码的基础上，稍微修改了一点。
Function BCFSJ(Index ...

老师：您修改的代码，C19:C30显示#N/A错误的问题解决了，但又出现了新的问题：当第一参数为0时，如D2输入 公式 =COUNT(BCFSJ(0,A5:A30))，可以显示不重复数字个数【10个】；D3输入公式=COUNTA(BCFSJ(0,A5:A30)) ，可以显示不重复数据个数【14个】，

   怎样保留第一参数为0时的原有计数功能，使D2、D3能够仍然显示正确的计数结果？

mzbao

WYS67 发表于 2019-4-1 10:20
老师：您修改的代码，C19:C30显示#N/A错误的问题解决了，但又出现了新的问题：当第一参数为0时，如D2输入 ...

D2和D3你可以使用Excel自己的公式统计，这种公式应该有好几种可以实现。如果你实在想用VBA实现，你可以直接在代码里统计好就可以了。

WYS67

mzbao 发表于 2019-4-1 13:23
D2和D3你可以使用Excel自己的公式统计，这种公式应该有好几种可以实现。如果你实在想用VBA实现，你可以直 ...

经验证：公式很强大！谢谢老师！

WYS67

mzbao 发表于 2019-4-1 13:23
D2和D3你可以使用Excel自己的公式统计，这种公式应该有好几种可以实现。如果你实在想用VBA实现，你可以直 ...

老师：怪哉！什么原因？--C列的区域数组公式，如果数据区域大于3万多行，就会显示#VALUE!错误。如选定C5:C36000,输入{   =BCFSJ(0,$A$5:$A$36000)，就会显示#VALUE!错误。

不能突破65336行的限制吗？现在的工作表都是一百多万行的。

mzbao

你把k、NumbersCount和NotRepeatCount的数据类型改成长整型（Long）试试，即：
Dim NotRepeat As Object, tStr As String, k%, rArr(), NumbersCount%, NotRepeatCount%
改成：
Dim NotRepeat As Object, tStr As String, k As Long, rArr(), NumbersCount As Long, NotRepeatCount As Long