yjh自定义函数

WYS67

最后行列.zip (22.57 KB, 下载次数: 6)

2020-3-10 09:58 上传

点击文件名下载附件

老师：如上图，有几点不明白，相烦老师解释一下--

1.  E11的       ""        UsedRange  ， UsedRange  代表什么意思，百度了一下，解释为：每个sheet中的一个矩形区域的左上角到右下角的单元格引用。这个矩形区域是恰好能容纳所有已使用单元格的最小矩形。比如，Sheets(1)的B3，E2，D4有数据，那么，这个Sheet的UsedRange的Address属性的值就是$B$2:$E$4；如此说来，=EndRc("A1:D16",,0)  指定的最小区域就是A4:C12了吧？


2.本工作表的最大行列号是E83，可是在C5输入公式=EndRc("") 后，却显示为0？


3.如上边右图，在C5输入公式=EndRc("") 时，会弹出循环引用警告！请问，在哪些单元格里输入公式=EndRc("")，才能显示E83这个正确结果？

WYS67

老师：麻烦您看看11楼反映的问题是怎么回事，如何解决？

WYS67

老师：麻烦您看看11楼反映的问题是怎么回事，如何解决？

yjh_27

WYS67 发表于 2020-3-10 16:41
老师：麻烦您看看11楼反映的问题是怎么回事，如何解决？

参见E13单元格说明。
这个自定义函数用于工作表是有限制的。

WYS67

yjh_27 发表于 2020-3-10 17:31
参见E13单元格说明。
这个自定义函数用于工作表是有限制的。

不能在工作表里输入公式 =EndRc("")   。感觉把C5里的公式=EndRc("")  清除后，就正常了！谢谢老师！虽然还是对需设置手动计算Application.Calculation = xlManual或启用迭代计算Application.Iteration = True 不理解，但绝大多数情况下，用不到=EndRc("") 这个公式。

WYS67

可以右拉下拉的动态区域.zip (31.04 KB, 下载次数: 11)

2020-3-13 05:54 上传

点击文件名下载附件

老师：EndRC的参数设置是 =ENDRC(指定区域,工作表名称,返回模式)，DataRC的参数设置是：=DataRC(返回模式,指定区域,工作表名称)，我觉得那个指定区域参数，最好不要用""，如：选定J23，输入公式 =ENDRC(J5:J22)  可以右拉复制公式至Q23，返回指定区域内各列的最大行号；同理，选定R5，输入公式 =ENDRC(J5:Q5)  可以下拉复制公式至R22，返回指定区域内各行的最大列号。

本来公式可以右拉下拉，但是把指定区域用""号围起来后，就不能智能复制公式了。

    '参数形式如：MyRange     指定范围,如果忽略则为UsedRange
    '                "A:B"     整列
    '                "6:26"    整行
    '                "a6:h26"  矩形区域
    '                "-1"      负数，函数范围caller
    '                ""        UsedRange

而 MyRange 的几个参数，除了最下边的 ""，剩余的取消引号，直接输入，必要时可以加 $  进行绝对或相对引用，便于拖曳。

一点小小的建议，供老师参考。

yjh_27

WYS67 发表于 2020-3-13 06:06
老师：EndRC的参数设置是 =ENDRC(指定区域,工作表名称,返回模式)，DataRC的参数设置是：=DataRC(返 ...

按你的建议，做成不同的函数。你试试

WYS67

yjh_27 发表于 2020-3-13 10:07
按你的建议，做成不同的函数。你试试

老师：已测试，很完美！如果能够像16楼截图里的R21那样，输入公式=ENDRC(J21:Q21)  ，当指定区域没有数据时，屏蔽显示的#VALUE!错误为空白就更好了！
老师，应该在ENDRC和DATARC的哪个地方，添加什么样的代码，才能实现：当指定区域没有数据时，屏蔽显示的#VALUE!错误为空白的功能？

yjh_27

WYS67 发表于 2020-3-13 11:02
老师：已测试，很完美！如果能够像16楼截图里的R21那样，输入公式=ENDRC(J21:Q21)  ，当指定区域没有数据 ...

end function 前

if IsError (ENDRC) then ENDRC=""

WYS67

yjh_27 发表于 2020-3-13 11:08
end function 前

if IsError (ENDRC) then ENDRC=""

老师，像下面那样，倒数第二句？  不行啊，仍然显示#VALUE!错误。

Function ENDRC(Optional MyRange As Range, Optional MySht As String, Optional mode = 0)
    '得到有数据的最后行号，两个参数均可选
    '参数形式如：MyRange     指定范围,如果忽略则为UsedRange
    '                A:B       整列
    '                6:26      整行
    '                a6:h26    矩形区域
    '                ""        UsedRange
    '            MySht       指工作表名，如果忽略则为活动工作表
    '                        在工作表中使用且MySht=活动工作表时，会引起循环引用，需设置手动计算Application.Calculation = xlManual或启用迭代计算Application.Iteration = True
    '            mode        返回模式,0~9最大行（行扫描优先），>9最大列（列扫描优先）
    '                0         最后相对地址
    '                1         最后行号
    '                2         最后列号
    '                3         最后列字母
    '                4         "最后行号,最后列号"
    '                5         最后绝对地址
    Dim rng As Range, sht As Worksheet, ar, i As Long, j As Long
    If MySht <> "" Then Set sht = Sheets(2) Else Set sht = ActiveSheet
    If MyRange Is Nothing Then Set rng = sht.UsedRange Else Set rng = Intersect(sht.UsedRange, sht.Range(MyRange.Address))
    If Not rng Is Nothing Then
        ar = rng.Value
        If Not IsArray(ar) Then
            If Len(ar) > 0 Then EndR = rng.Row: EndC = rng.Column
        ElseIf mode < 10 Then
            For i = UBound(ar) To 1 Step -1
                For j = UBound(ar, 2) To 1 Step -1
                    If Len(ar(i, j)) Then
                        EndR = i - 1 + rng.Row
                        EndC = j - 1 + rng.Column
                        i = 1
                        Exit For
                    End If
                Next
            Next
        Else
            For j = UBound(ar, 2) To 1 Step -1
                For i = UBound(ar) To 1 Step -1
                    If Len(ar(i, j)) Then
                        EndR = i - 1 + rng.Row
                        EndC = j - 1 + rng.Column
                        j = 1
                        Exit For
                    End If
                Next
            Next
            mode = mode Mod 10
        End If
        If mode = 1 Then
            ENDRC = EndR
        ElseIf mode = 2 Then
            ENDRC = EndC
        ElseIf mode = 3 Then
            ENDRC = Split(Cells(EndR, EndC).Address, "$")(1)
        ElseIf mode = 4 Then
            ENDRC = EndR & "," & EndC
        ElseIf mode = 5 Then
            ENDRC = Cells(EndR, EndC).Address
        Else
            ENDRC = Cells(EndR, EndC).Address(0, 0)
        End If
    End If
    If IsError(ENDRC) Then ENDRC = ""
End Function