如何使用Excel VBA中的Find函数修复错误 [英] How to fix the error using Find function in Excel VBA
问题描述
我试图从另一张表中搜索一个值,而不是使用.FIND函数
私有功能搜索(rng As Range, FindString As String)As Range
With rng
Set Search = .Find(what:= FindString,_
After:=。Cells(.Cells.Count),_
LookIn := xlValues,_
lookat:= xlWhole,_
SearchOrder:= xlByRows,_
SearchDirection:= xlNext,_
MatchCase:= False)
结束With
有趣的是,如果我输入
搜索(范围(DataSheet!A1:Z1000),启动)
它有效。当我尝试
SearchString ='& Selected_sheet&'!A1:Z1000
它不工作Selected_sheet只是一个工作表(在这种情况下,它是DataSheet)。使用
搜索(Range(SearchString),STARTING)
错误:运行时错误'91'
对象变量或块变量不设置
可能是实际问题?
您的方法不正确,但我建议不要传递这样的范围。
使用此
搜索(表格(DataSheet)。范围(A1:Z1000), 开始)
或
Selected_sheet =DataSheet
pre>
搜索(Sheets(Selected_sheet).Range(A1:Z1000),STARTING)
您的代码中的
Range(SearchString)
没有任何错误。应该工作您如何致电搜索
?希望这样吗?Dim Ret As Range'< ~~~
Selected_sheet =DataSheet
SearchString ='& Selected_sheet& '!A1:Z1000
Set Ret = Search(Range(SearchString),STARTING)
原因:该函数返回一个
范围
,所以你不能只调用它,而不需要$ code> Set Ret = ... 其中Ret
再次声明为范围
。
编辑
如果您的
搜索
函数不返回范围,并尝试使用Ret
。看到这个例子。Dim Ret As Range
Selected_sheet =DataSheet
SearchString ='& Selected_sheet& '!A1:Z1000
Set Ret = Search(Range(SearchString),STARTING)
'< ~~这将给你RUN-TIME ERROR'91' STARTING未找到
Debug.Print Ret.Address
那么我们如何处理这个
尝试这个
Dim Ret As Range
Selected_sheet =DataSheet
SearchString ='& Selected_sheet& '!A1:Z1000
设置Ret =搜索(范围(SearchString),启动)
如果Not Ret没有则
Debug.Print Ret.Address
Else
Debug.Print搜索没有返回任何结果
结束如果
I tried to search a value from another sheet rather using the .FIND function
Private Function Search(rng As Range, FindString As String) As Range With rng Set Search = .Find(what:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With
The funny thing is that if I enter
Search(Range("'DataSheet'!A1:Z1000"),"STARTING")
It works. When I try
SearchString = "'" & Selected_sheet &"'!A1:Z1000"
It does not work Selected_sheet is just a name of a worksheet (In this case, it is "DataSheet"). Using
Search(Range(SearchString ),"STARTING") Error: RUN-TIME ERROR '91' Object variable or With Block Varibale Not Set
What could be the actual problem?
解决方案Your method is not incorrect but I recommend not passing your range like that.
Use this
Search(Sheets("DataSheet").Range("A1:Z1000"),"STARTING")
or
Selected_sheet = "DataSheet" Search(Sheets(Selected_sheet).Range("A1:Z1000"),"STARTING")
BTW there is nothing wrong with
Range(SearchString)
in your code. It should work. How are you callingSearch
? Hope like this?Dim Ret As Range '<~~~ Selected_sheet = "DataSheet" SearchString = "'" & Selected_sheet & "'!A1:Z1000" Set Ret = Search(Range(SearchString), "STARTING")
Reason: The function returns a
Range
so you cannot just call it withoutSet Ret = ...
WhereRet
is again declared as aRange
.EDIT
You can also get that error if your
Search
function doesn't return a range and you try using thatRet
. See this example.Dim Ret As Range Selected_sheet = "DataSheet" SearchString = "'" & Selected_sheet & "'!A1:Z1000" Set Ret = Search(Range(SearchString), "STARTING") '<~~ This will give you RUN-TIME ERROR '91' if "STARTING" is not found Debug.Print Ret.Address
So how do we tackle this
Try this
Dim Ret As Range Selected_sheet = "DataSheet" SearchString = "'" & Selected_sheet & "'!A1:Z1000" Set Ret = Search(Range(SearchString), "STARTING") If Not Ret Is Nothing Then Debug.Print Ret.Address Else Debug.Print "Search Didn't return any results" End If
这篇关于如何使用Excel VBA中的Find函数修复错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!