我已经构建了一个UDF，以将搜索列表中匹配项的所有偏移量的逗号分隔值返回给我。就像一个vlookup，它返回一个列表而不是一个值。

我从子例程运行它进行测试，并且效果很好，当我尝试从电子表格运行它时出错

这是我的测试子例程：

Sub TestHightlightFindValues()
    Debug.Print FindOffsetMatches(Range("P4").Text,Sheets("Org details").Range("A2:A10119"),1)
End Sub

这是我在Excel中的测试功能：

=FindOffsetMatches(P4,'Org details'!$A$2:$A$10119,1)

这是功能代码

Function FindOffsetMatches(SearchValue As String,SearchRange As Range,ReturnOffset As Long,Optional MyDelimiter As String = ",")
Dim FirstFound As String,FoundCell As Range,rng As Range,LastCell As Range,ReturnRange As Variant,X As Long,ReturnString As String
On Error GoTo ErrorFound
Set LastCell = SearchRange.Cells(SearchRange.Cells.Count)
Set FoundCell = SearchRange.Find(what:=SearchValue,after:=LastCell)
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If
Set rng = FoundCell
Do Until FoundCell Is Nothing
    Set FoundCell = SearchRange.FindNext(after:=FoundCell)
    Set rng = Union(rng,FoundCell) '<-- This is where it errors,it thinks that FoundCell is empty only when called from an Excel sheet but not when called from a subroutine
    If FoundCell.Address = FirstFound Then Exit Do
Loop
ReturnRange = Split(rng.Address,",")
ReturnString = Sheets(SearchRange.Worksheet.Name).Range(ReturnRange(LBound(ReturnRange))).Offset(0,ReturnOffset).Text
For X = LBound(ReturnRange) To UBound(ReturnRange)
    If X > LBound(ReturnRange) Then ReturnString = ReturnString & MyDelimiter & Sheets(SearchRange.Worksheet.Name).Range(ReturnRange(X)).Offset(0,ReturnOffset).Text
Next
FindOffsetMatches = ReturnString
Exit Function
'Error Handler
NothingFound:
FindOffsetMatches = "Not found"
Exit Function
ErrorFound:
FindOffsetMatches = "#N/A"
End Function

有任何想法为什么会这样？

谢谢。