将宏传给自定义项

问题描述

我希望将下面的宏传输到UDF，但我不知道如何将其传输到UDF。

我想要一个UDF，我在其中选择查找字符串并在放置UDF的单元格中返回它。

有人能帮帮我吗？

Sub Find_pipe()
    Dim Findstring As String
    Dim Location As String
    Dim Rng As Range
    
    Sub Find_First()
    Dim Findstring As String
    Dim Rng As Range
    Findstring = InputBox("vul naam van leiding in")
    
    If Trim(Findstring) <> "" Then
        With Sheets("scenario 1V2").Range("A1:BP150")
            Set Rng = .Find(What:=Findstring, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng.Offset(1), True
                Application.Goto ThisWorkbook.Worksheets("D en L berekening").Range("A1"), True
                ThisWorkbook.Worksheets("D en L berekening").Range("U10").Value = Rng.Offset(1).Value
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

推荐答案

试试：

Function FindPipe(Findstring As String)
    Application.Volatile 'You need this if your UDF needs to update after changes in
                         '   the search range
    Dim f As Range
    If Trim(Findstring) <> "" Then
        With ThisWorkbook.Sheets("scenario 1V2").Range("A1:BP150")
            Set f = .Find(What:=Findstring, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
        End With
        If Not f Is Nothing Then
            FindPipe = f.Offset(1).Value
        Else
            FindPipe = "Not found"
        End If
    Else
        FindPipe = ""
    End If
End Function