excel - 如何在 MyVlookup 函数中使用通配符？

Question

在 Excel 中使用 调用以下函数时=MyVlookup(B1,Sheet1!A:A,1) 我得到我想要的。

Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant

Dim cl As Range
For Each cl In c.Columns(1).Cells
If UCase(Lval) = UCase(cl) Then
MyVlookup = cl.Offset(, oset - 1)
Exit Function
End If
Next
End Function

但我想在 周围使用通配符B1 ，例如 "*"&B1&"*" .当我这样做时，我得到一个错误(#ARG!)。我尝试使用双引号，但它也不起作用。我应该怎么做才能克服这个？

Answer 1

Function MyVlookup(Lval As String, c As Range, oset As Long) As Variant

Dim cl As Range

    Lval = UCase(Lval)

    For Each cl In c.Columns(1).Cells
        If UCase(cl) Like Lval Then
            MyVlookup = cl.Offset(, oset - 1)
            Exit Function
        End If
    Next
End Function