VBA 中的 .Find() 方法出现“类型不匹配”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28460641/
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
'Type Mismatch' Error with .Find() method in VBA
提问by AllyB
I'm writing a sub in Excel VBA, and it keeps giving me a 'Type Mismatch' error message when trying to assign the result of a .Find() to a Range variable. I feel pretty confident that my types are appropriate, so perhaps there's a syntax error somewhere?
我正在 Excel VBA 中编写一个子程序，当尝试将 .Find() 的结果分配给 Range 变量时，它不断给我一个“类型不匹配”的错误消息。我非常有信心我的类型是合适的，所以也许某处有语法错误？
Help would be GREATLY appreciated: (Line preceded by asterisks is where error is thrown)
非常感谢帮助：（以星号开头的行是引发错误的地方）
Sub totalTiger(fCode As String, project As String, ttls() As Double)
'Set shorcuts to Worksheets
Dim this As Workbook: Set this = ThisWorkbook
Dim proj As Worksheet: Set proj = this.Worksheets(project)
'Dim req variables
Dim tRng As Range: Set tRng = proj.Range("A:A").Find(What:="Program Description") 'Establish where Staff data ends and Tiger data begins
***Dim rng As Range: Set rng = proj.Range("C:C").Find(What:=fCode, After:=tRng) 'First fCode entry***
'For each fCode entry BEFORE the Tiger data, sum the assignments by month
Do While Not rng Is Nothing And rng.row > tRng.row
'For each month
For col = 4 To 15
'Add this month's assignment to our running total
ttls(col - 4) = ttls(col - 4) + CDbl(proj.Cells(rng.row, col).Value)
Next
'Update the rng reference
Set rng = proj.Range("C:C").Find(What:=fCode, After:=rng)
Loop
End Sub
回答by genespos
I think the problem is in "After:=tRng": it may be out of the range of the "find"
我认为问题出在“After:=tRng”：它可能超出“find”的范围
Dim rng As Range: Set rng = proj.Range("C:C").Find(What:=fCode, After:=tRng)
Try removing "After:=tRng" and, if it works after removing, then try to insert a correct range.
尝试删除“After:=tRng”，如果删除后它有效，则尝试插入正确的范围。
Dim rng As Range: Set rng = proj.Range("C:C").Find(What:=fCode)
I'm not sure that's what you need, but you can try:
我不确定这是否是您需要的，但您可以尝试：
Dim rng: Set rng = proj.Range("C:C").Find(What:=fCode, After:=proj.Range("C" & tRng.Row))
It finds the first 'fCode' starting by the row where was found "Program Description"
它从找到“程序描述”的行开始找到第一个“fCode”
回答by MicrosoftShouldBeKickedInNuts
If you are scratching your head because all logical explanations (such as those above) do not fit, it may be CORRUPTION. I have a live workbook example where, as absurd as it looks, a sheet (with nothing on it; hitting control end lands on A1) reproducibly shows ?activesheet.usedrange.address giving "$B$1:$A$1" and ?activesheet.usedrange.count gives 0.
如果你因为所有的逻辑解释（例如上面的那些）都不合适而挠头，那可能是 CORRUPTION。我有一个实时工作簿示例，尽管看起来很荒谬，但工作表（上面没有任何内容；点击控制端落在 A1 上）可重现地显示 ?activesheet.usedrange.address 给出“$B$1:$A$1”和 ?activesheet .usedrange.count 给出 0。
Thus a most rigorous coding defense should test, BEFORE doing the find,
因此，最严格的编码防御应该在进行查找之前进行测试，
If Not Intersect(tRng, proj.Range("A:A")) Is Nothing
for the example above, and before any general range expression for {expression}.find,
对于上面的例子，在 {expression}.find 的任何一般范围表达式之前，
If Not Intersect(tRng, {expression}) Is Nothing
Your "Else" could drill into the issue, e.g.
您的“其他”可以深入研究问题，例如
msgbox tRng.address & "/" & {expression}.address
The If statement would prevent the Type Mismatch error, not only if you "illegally" or improperly set an "After:" that is not in the search range, but also for at least some cases of corrupt workbooks such as I have encountered IRL. Corrupt workbooks are scary, brutally dangerous, but Microsoft won't address the problem, so corruption is going to happen; testing such as above would at least serve to sound a potential corruption alarm (if applicable), as well as serve the basic function of simply identifying if you had an (After and {expression}) combination that is not permitted.
If 语句将防止类型不匹配错误，不仅在您“非法”或不正确地设置不在搜索范围内的“之后：”时，而且至少在某些情况下损坏工作簿（例如我遇到 IRL）时也是如此。腐败的工作簿很可怕，非常危险，但微软不会解决这个问题，所以腐败会发生；像上面这样的测试至少可以发出潜在的损坏警报（如果适用），并提供基本功能，即简单地识别您是否有不允许的 (After 和 {expression}) 组合。
回答by El Scripto
It is possible that tRng is set to nothing, so the problem is not with the returned value of find, but rather with the parameter you give it.
tRng 可能设置为空，所以问题不在于 find 的返回值，而在于您给它的参数。
