VBA Find 函数不适用于变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20125533/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Find function not working with variable
提问by ChiomaJenni
I am creating a sheet that needs the application to search excel sheet for a value that is saved in a variable.
I am using the ".Find" function for this.
My problem is that the value being searched for can not be found while it is stored in a variable name although it works when i input an actual value.
我正在创建一个工作表，需要应用程序在 Excel 工作表中搜索保存在变量中的值。
我为此使用了“.Find”功能。
我的问题是正在搜索的值存储在变量名称中时无法找到，尽管它在我输入实际值时有效。
For example:
例如：
this works
这有效
Dim cellersd, celltid, ersdcol, tidcol
Set cellersd = book.Worksheets(mon & yer).Range("5:5").Find("28/08/2013")
Set celltid = book.Worksheets(mon1 & yer1).Range("5:5").Find("30/09/2013")
If cellersd Is Nothing Then
MsgBox "not found"
Else
MsgBox cellersd.Column
End If
If celltid Is Nothing Then
MsgBox "not found"
Else
MsgBox celltid.Column
End If
this doesnt work
这不起作用
rsd = Worksheets("workload").Range("p4").Value
tid = Worksheets("workload").Range("p3").Value
...
Dim cellersd, celltid, ersdcol, tidcol
Set cellersd = book.Worksheets(mon & yer).Range("5:5").Find(rsd)
Set celltid = book.Worksheets(mon1 & yer1).Range("5:5").Find(tid)
If cellersd Is Nothing Then
MsgBox "not found"
Else
MsgBox cellersd.Column
End If
If celltid Is Nothing Then
MsgBox "not found"
Else
MsgBox celltid.Column
End If
The ... in the code just shows that i have other codes inbetween which perform different actions
代码中的 ... 只是表明我还有其他代码可以执行不同的操作
Can you spot my error? i am also open to suggestions on other search methods that could equally work. Thanks a lot.
你能发现我的错误吗？我也愿意接受其他同样有效的搜索方法的建议。非常感谢。
采纳答案by Tylor Hess
Perhaps try
也许试试
rsd = Worksheets("workload").Range("p4").Text
tid = Worksheets("workload").Range("p3").Text
or if you wish to keep .Valueor .Textdoes not work you chould try to convert to string:
或者，如果您希望保留.Value或.Text不起作用，您可以尝试转换为字符串：
rsd = str(rsd)
tid = str(tid)
I believe the .Valuereturns a date and not a string. and the working code is using a string.
我相信.Value返回一个日期而不是一个字符串。并且工作代码正在使用字符串。
回答by Help
This Should work
这应该工作
Use What:=rsdif it's variable, use What:="something"if it's exact word between "".
使用What:=rsd如果它的变量，使用What:="something"，如果它是在“”确切的词。
If you need find variable from variable then use:
如果您需要从变量中查找变量，请使用：
Set find1 = variable1.Find(What:=variable2, LookIn:=xlValues)
'Both work
rsd = Worksheets("workload").Range("P4")
tid = Sheets("workload").Range("P3")
...
Dim ersdcol, tidcol
Dim cellersd As Object
Dim celltid As Object
Set cellersd = Sheets("mon & yer").Range("5:5").Find(What:=rsd, LookIn:=xlValues)
Set celltid = Sheets("mon1 & yer1").Range("5:5").Find(What:=tid, LookIn:=xlValues)
If cellersd Is Nothing Then
MsgBox "not found"
Else
MsgBox cellersd.Column
End If
If celltid Is Nothing Then
MsgBox "not found"
Else
MsgBox celltid.Column
End If
