使用FindNext的UDF似乎中止而没有警告 [英] UDF using FindNext seems to abort without warning

问题描述

我具有以下用户定义的函数，该函数尝试在范围内找到第n个非空单元格:

I have the following user-defined function, which attempts to find the nth non-empty cell in a range:

Option Explicit

Function finn_prioritert_oppgave(nummer As Long) As String

  Dim i As Long, r As Range, c As Range

  Set r = Range(PDCA.Range("L9"), PDCA.Range("L1048576").End(xlUp))
  i = 1

  Set c = r.Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole)

  While (Not c Is Nothing) And (Intersect(c, PDCA.Rows(9)) Is Nothing) And (i < nummer)

    Debug.Print c
    Set c = r.FindNext(c)
    Debug.Print c
    Debug.Print CBool(c Is Nothing)
    i = i + 1

  Wend

  If c Is Nothing Then
    finn_prioritert_oppgave = "#N/A"
  Else
    finn_prioritert_oppgave = c.Offset(0, -10).Value
  End If

End Function

使用1作为参数运行它可以正常工作，大概是因为它没有进入While循环并按下FindNext，但是使用更大的值作为参数运行它会导致从中调用该单元格显示#VALUE!-警报.

Running it with 1 as the argument works fine, presumably because it doesn't enter the While-loop and hit the FindNext, but running it with any larger value as the argument causes the cell it is called from to display a #VALUE!-alert.

查看我即时窗口中显示的内容也确实很奇怪，因为尽管我没有收到警报，但执行FindNext之后的两条Debug.Print消息却无法打印.

Looking at what is displayed in my immediate-window is also really strange, as the two Debug.Print messages after doing the FindNext doesn't print, although I get no alerts.

我在即时窗口中得到的输出(使用2作为其参数调用的UDF只是一个x:

The output I get in the immediate window, with the UDF invoked with 2 as its argument is simply an x:

调用该函数的区域类似于此(第一行是使用1作为参数调用的UDF，第二个是它以2作为参数调用)，而包含数据的区域看起来像此.

The area the function is called from looks like this (the first line is the UDF invoked with 1 as the argument, the second is it invoked with 2 as the argument), while the area containing data looks like this.

所以我想知道的是，为什么FindNext找不到范围内的第二个非空单元格，为什么函数在没有任何警告的情况下中止?

So what I wonder is, why does FindNext fail to find the second non-empty cell in the range, and why does the function abort without any warning?

推荐答案

FindNext在UDF中不起作用.您只需重复原始的Find操作.

FindNext doesn't work in UDFs. You have to simply repeat the original Find operation.

这篇关于使用FindNext的UDF似乎中止而没有警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！