Thread: Multiple lookup function fails when called as a UDF

Morning all (it's morning in the UK, at least) - this is a problem I've come across in the past and worked around, but I wonder if one of the gurus here can tell me what the issue is.

I have a fairly straightforward function to concatenate the results from (effectively) a VLOOKUP on a range where there are multiple matches ...

Function MultiVLookup(vMatchCriteria As Variant, rngLookUpArea As Range, lOffset As Long, Optional sDelimiter = ",") As String

Dim rngMatchValue As Range
Dim sFirstAddress As String
Dim sTmpReturn As String

sTmpReturn = ""

With rngLookUpArea
  
  Set rngMatchValue = .Find(vMatchCriteria, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
  
  If Not rngMatchValue Is Nothing Then
  
    sFirstAddress = rngMatchValue.Address
    
    Do
    
      sTmpReturn = sTmpReturn & rngMatchValue.Offset(0, lOffset).Value & sDelimiter
      
      Set rngMatchValue = .FindNext(rngMatchValue)
      
    Loop Until rngMatchValue.Address = sFirstAddress
    
  End If
  
End With

If Len(sTmpReturn) > 0 And Len(sDelimiter) > 0 Then
  sTmpReturn = Left(sTmpReturn, Len(sTmpReturn) - Len(sDelimiter))
End If

MultiVLookup = sTmpReturn
  
End Function

If I call this from a VBA subroutine it works fine, and produces the expected results:

Sub TestCall()

MsgBox MultiVLookup("A", ThisWorkbook.Sheets(1).Range("A1:A3"), 1)

End Sub

If, however, I call it as a UDF it fails. A quick bit of debugging shows that this is because the .FindNext method returns an empty range, which shouldn't be possible, as there are both more matches within the range and, even if there weren't, there must have been at least one match, so it should loop back to that.

Things I've tried:

1. Naming the range explicitly, rather than using a With statement - no difference.
2. Defining another range within the function and setting it to the search range before the first search - no difference.

However, the brainwave I had half-way through typing this post was to replace the .FindNext statement with:

Set rngMatchValue = .Find(vMatchCriteria, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, after:=rngMatchValue)

And that does work as a UDF.

So, I guess my question not isn't so much "How do I make this work" as it is "Why the hecky-thump didn't it work with .FindNext". Anybody care to enlighten me?

Thanks.

I don't know why but it just doesn't. It was not until Excel 2002 that Find worked in UDFs either.

Yes. I suspect (but do not know for sure) that it is a similar reason to why properties like currentregion and currentarray do not work in UDFs.

It's probably more a conceptual thing...

Function MultiVLookup_snb(vMatchCriteria As Variant, rngLookUpArea As Range, lOffset As Long, Optional sDelimiter = ",") As String
  MultiVLookup_snb = ""
  MultiVLookup_snb = Join(Filter(Evaluate("transpose(If(" & rngLookUpArea.Resize(, 1).Address & "=""" & vMatchCriteria & """," & rngLookUpArea.Resize(, 1).Offset(, lOffset).Address & ",""~""))"), "~", False), sDelimiter)
End Function

I confess I do not know what you mean by that.

Oh, and that function won't lookup numbers.

Function MultiVLookup_snb(vMatchCriteria As Variant, rngLookUpArea As Range, lOffset As Long, Optional sDelimiter = ",") As String
  MultiVLookup_snb = ""
  If Not IsNumeric(vMatchCriteria) Then vMatchCriteria = Chr(34) & vMatchCriteria & Chr(34)
  MultiVLookup_snb = Join(Filter(Evaluate("transpose(If(" & rngLookUpArea.Resize(, 1).Address & "=" & vMatchCriteria & "," & rngLookUpArea.Resize(, 1).Offset(, lOffset).Address & ",""~""))"), "~", False), sDelimiter)
End Function

That one won't lookup numbers stored as text.