将宏传给自定义项 [英] Transfer macro to UDF
本文介绍了将宏传给自定义项的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我希望将下面的宏传输到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
注意:要搜索的范围是在UDF中硬编码的,因此如果更新了搜索范围,Excel不知道重新计算您的UDF。我添加了Application.Volatile
来解决这一问题,但如果您有很多公式指向该UDF,它可能会减慢您的工作簿速度。
这篇关于将宏传给自定义项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文