在 Excel VBA 中定位单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8301285/
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
Locating Cell Values in Excel VBA
提问by AME
Using: Excel 2007/Win 7
使用：Excel 2007/Win 7
First, I created a subroutine to find the dynamic range of a worksheet called 'WIP':
首先，我创建了一个子程序来查找名为“WIP”的工作表的动态范围：
Sub GetWIPRange()
Dim WIPrng1 As Range
Dim WIPrng2 As Range
Sheets("WIP").Activate
Set WIPrng1 = Cells.find("*", [a1], , , xlByRows, xlPrevious)
Set WIPrng2 = Cells.find("*", [a1], , , xlByColumns, xlPrevious)
If Not WIPrng1 Is Nothing Then
Set WIPrng3 = Range([a1], Cells(WIPrng1.Row, WIPrng2.Column))
Application.Goto WIPrng3
Else
MsgBox "sheet is blank", vbCritical
End If
End Sub
Now I want to find a given contract number within the range defined above:
现在我想在上面定义的范围内找到给定的合同号：
Sub find()
Dim find As Long
find = Application.WorksheetFunction.Match("545499", Range(WIPrng3.Parent.Name & "!" & WIPrng3.Address), 0)
MsgBox "Found at row : " & find
End Sub
But the error I get from the code above is:
但是我从上面的代码中得到的错误是：
Run-time error '91': Object variable With block variable not set.
运行时错误“91”：未设置块变量的对象变量。
- How can I fix this code so that it returns the row number of the value I'm seeking?
- Is there a more efficient way of finding cell values using VBA? For example, if I have many worksheets and I want to search all worksheets and return a value's specific row number and worksheet location.
- 如何修复此代码，使其返回我正在寻找的值的行号？
- 是否有使用 VBA 查找单元格值的更有效方法？例如，如果我有很多工作表并且我想搜索所有工作表并返回值的特定行号和工作表位置。
Many thanks!
非常感谢！
采纳答案by Dick Kusleika
Where is WIPrng3 defined? Is it defined as Public? The problem is that WIPrng3 has gone out of scope by the time you run "find" and is therefore Nothing. You can check for Nothing in your "find" code and run the Get procedure if needed. Like this
WIPrng3 在哪里定义？它被定义为公共吗？问题是当您运行“查找”时 WIPrng3 已经超出范围，因此是 Nothing。您可以检查“查找”代码中的 Nothing 并在需要时运行 Get 过程。像这样
Sub find()
Dim find As Long
If WIPrng3 Is Nothing Then GetWIPRange
find = Application.WorksheetFunction.Match("545499", Range(WIPrng3.Parent.Name & "!" & WIPrng3.Columns(1).Address), 0)
MsgBox "Found at row : " & find
End Sub
Two things to note: If WIPrng3 returns a multicolumn range, MATCH will fail with a 1004 error. MATCH only works on a single column or row. In the example above, I restricted WIPrng3 to the first column in the MATCH function to avoid this. You didn't have this in your code.
需要注意的两件事：如果 WIPrng3 返回多列范围，MATCH 将失败并返回 1004 错误。MATCH 仅适用于单列或单行。在上面的例子中，我将 WIPrng3 限制在 MATCH 函数的第一列以避免这种情况。你的代码中没有这个。
Another thing is that you're looking for the text string "545499", not the number 545499. If your range contains the number and not the string, you'll get an error. You can trap that error with an On Error statement and handle appropriately.
另一件事是您正在寻找文本字符串“545499”，而不是数字 545499。如果您的范围包含数字而不是字符串，您将收到错误消息。您可以使用 On Error 语句捕获该错误并进行适当处理。
Finally, I don't see the advantage to defining WIPrng3 (but I can't see the whole of what you're doing). You could easily use
最后，我没有看到定义 WIPrng3 的优势（但我看不到你在做什么）。您可以轻松使用
Sub Find2()
Dim lRow As Long
On Error Resume Next
lRow = Application.WorksheetFunction.Match("545499", Sheets("WIP").UsedRange.Columns(1), 0)
If lRow > 0 Then
'add where the used range starts in case it's not row 1
MsgBox "Found at row : " & lRow + Sheets("WIP").UsedRange.Cells(1).Row - 1
Else
MsgBox "Not found"
End If
End Sub
You might end up looking through a larger range, but it won't appreciably affect performance.
您最终可能会查看更大的范围，但这不会明显影响性能。
I added the On Error in this example so you could see how it works. Don't put the On Error in there until you've tested it because it will mask all the other errors.
我在这个例子中添加了 On Error 以便你可以看到它是如何工作的。在测试之前不要将 On Error 放在那里，因为它会掩盖所有其他错误。
Charles Williams did some nice analysis on finding efficiency here http://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/
查尔斯·威廉姆斯 (Charles Williams) 在http://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/
