本文介绍了将宏传给自定义项的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我希望将下面的宏传输到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,它可能会减慢您的工作簿速度。更多推荐
将宏传给自定义项
发布评论