如何使用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
搜索（Sheets（Selected_sheet）.Range（A1：Z1000），STARTING）
  pre> 
 
 
您的代码中的 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 calling Search? 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 without Set Ret = ... Where Ret is again declared as a Range.

EDIT

You can also get that error if your Search function doesn't return a range and you try using that Ret. 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屋！