excel - UDF using FindNext seems to abort without warning

Question

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

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.

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.

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

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.

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?

Answer 1

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