怎样使第二参数的条件区域指定可以直接引用数字常量？

WYS67

如截图C1:E3列所示，自定义函数的第二参数为条件区域：如选定C5:C15460，输入区域数组公式 =YILOUHH($A$5:$A$15460,C1:C3)，即可显示当A列数据指定1时，上下次出现之间的行号差；选定E5:E15460，输入区域数组公式 =YILOUHH($A$5:$A$15460,E1:E3)，即可显示当A列数据指定为>=0,<=2范围内时，上下次出现之间的行号差。


请问：能不能修改下面代码的第二参数为：既能够调用单元格指定区域，也能够直接指定数字常量进行计算，


  如输入=YILOUHH($A$5:$A$15460,1)，自动执行C列的运算；输入=YILOUHH($A$5:$A$15460,{0,2})，则自动执行D列的运算；输入=YILOUHH($A$5:$A$15460,{0,1,2})，则自动执行E列的运算？

如果可以，怎样修改第一句代码--
Public Function YILOUHH(rgSource As Range, Optional rgConditions As Range)

遗漏行数.zip (507.48 KB, 下载次数: 3)

2019-6-25 07:16 上传

点击文件名下载附件

lsdongjh

1. Public Function YILOUHH(rgSource As Range, Optional varConditions As Variant)
   
2.     Application.Volatile True
   
3.     Dim strConditions(1 To 3) As String, intStart As Integer, intEnd As Integer
   
4.     Dim intIndex As Integer, intCurId As Integer, intType As Integer
   
5.     Dim arrSource As Variant, arrResult As Variant
   
6.     Dim objFind As Object
   
7.    
   
8.     Dim lngRow As Long
   
9.    
   
10.     arrSource = rgSource
    
11.     ReDim arrResult(LBound(arrSource) To UBound(arrSource), 1 To 1)
    
12.     Set objFind = CreateObject("Scripting.Dictionary")
    
13.    
    
14.     '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
15.     '条件规整
    
16.     If Not IsMissing(varConditions) Then
    
17.         '判断条件是否为数组
    
18.         If IsArray(varConditions) Then
    
19.             Select Case TypeName(varConditions)
    
20.                 Case "Range"
    
21.                     intEnd = varConditions.Count
    
22.                     If intEnd > 3 Then intEnd = 3
    
23.                     For intIndex = 1 To 3
    
24.                         strConditions(intIndex) = Trim(varConditions(intIndex).Value)
    
25.                     Next
    
26.                 Case "Variant()"
    
27.                     intStart = LBound(varConditions)
    
28.                     intEnd = UBound(varConditions)
    
29.                     If intStart + 2 > intEnd Then intEnd = intStart + 2
    
30.                     intCurId = 1
    
31.                     For intIndex = intStart To intEnd
    
32.                         strConditions(intCurId) = Trim(varConditions(intIndex))
    
33.                         intCurId = intCurId + 1
    
34.                     Next
    
35.             End Select
    
36.         Else
    
37.             strConditions(1) = varConditions
    
38.         End If
    
39.     End If
    
40.    
    
41.     '判断条件情况
    
42.     If strConditions(1) <> "" And strConditions(2) = "" And strConditions(3) = "" Then
    
43.         intType = 1 '只有一个条件单元格
    
44.     ElseIf strConditions(1) <> "" And strConditions(2) <> "" And strConditions(3) = "" Then
    
45.         intType = 2 '前两个单元格有值,或者的关系
    
46.     ElseIf strConditions(1) <> "" And strConditions(2) <> "" And strConditions(3) <> "" Then
    
47.         intType = 3 '三个单元格都有值,或者的关系
    
48.     ElseIf strConditions(1) <> "" And strConditions(2) = "" And strConditions(3) <> "" Then
    
49.         intType = 4 '一、三单元格有值，按>=1 且 <=3 规整
    
50.     ElseIf strConditions(1) = "" And strConditions(2) = "" And strConditions(3) = "" Then
    
51.         intType = 5 '无条件
    
52.     End If
    
53.    
    
54.     ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
55.     '数据规整
    
56.     For lngRow = LBound(arrSource) To UBound(arrSource)
    
57.         Select Case intType
    
58.             Case 1
    
59.                 If arrSource(lngRow, 1) <> strConditions(1) Then
    
60.                     arrSource(lngRow, 1) = ""
    
61.                 End If
    
62.             Case 2
    
63.                 If arrSource(lngRow, 1) = strConditions(1) Or arrSource(lngRow, 1) = strConditions(2) Then
    
64.                     arrSource(lngRow, 1) = "A"
    
65.                 Else
    
66.                     arrSource(lngRow, 1) = ""
    
67.                 End If
    
68.             Case 3
    
69.                 If arrSource(lngRow, 1) = strConditions(1) Or arrSource(lngRow, 1) = strConditions(2) Or arrSource(lngRow, 1) = strConditions(3) Then
    
70.                     arrSource(lngRow, 1) = "A"
    
71.                 Else
    
72.                     arrSource(lngRow, 1) = ""
    
73.                 End If
    
74.             Case 4
    
75.                 If arrSource(lngRow, 1) >= Val(strConditions(1)) And arrSource(lngRow, 1) <= Val(strConditions(3)) Then
    
76.                     arrSource(lngRow, 1) = "A"
    
77.                 Else
    
78.                     arrSource(lngRow, 1) = ""
    
79.                 End If
    
80.         End Select
    
81.     Next
    
82.    
    
83.     '''''''''''''''''''''''''''''''''''''''''''''''
    
84.     '数据查找计算
    
85.    
    
86.     For lngRow = LBound(arrSource) To UBound(arrSource)
    
87.         arrResult(lngRow, 1) = ""
    
88.         If arrSource(lngRow, 1) <> "" Then
    
89.             If objFind.Exists(arrSource(lngRow, 1)) = True Then
    
90.                 arrResult(lngRow, 1) = lngRow - objFind(arrSource(lngRow, 1))
    
91.             End If
    
92.             objFind(arrSource(lngRow, 1)) = lngRow
    
93.         End If
    
94.     Next
    
95.    
    
96.     Set objFind = Nothing
    
97.     YILOUHH = arrResult
    
98.    
    
99. End Function

复制代码

WYS67

lsdongjh 发表于 2019-6-25 09:46

非常感谢老师!  经测试，C、E列直接指定数字常量可以显示正确结果，但选定D5:D15460，修改D列公式=YILOUHH($A$5:$A$15460,D1:D3)为=YILOUHH($A$5:$A$15460,{0,2})后，D列显示#VALUE!错误！

WYS67

lsdongjh 发表于 2019-6-25 09:46

老师：可不可以设置：{}里面，用{n1,n2}代表两个条件：条件1，条件2【如D列】；用{n:m}代表条件的小大范围【如E列】？

lsdongjh

为了保持一致，请输入{n,,m}

lsdongjh

改一句话，逻辑有问题了
If intStart + 2 > intEnd Then intEnd = intStart + 2
改为
  If intEnd - intStart > 2 Then intEnd = intStart + 2

WYS67

lsdongjh 发表于 2019-6-25 15:29
改一句话，逻辑有问题了
If intStart + 2 > intEnd Then intEnd = intStart + 2
改为

按5楼，{n,,m}则无法输入公式；6楼修改代码彻底解决问题！再次感谢老师！