首页 文章

在Excel VBA中查找单元格值

提问于
浏览 次
1

使用：Excel 2007 / Win 7

首先，我创建了一个子程序来查找名为“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

现在我想在上面定义的范围内找到给定的 Contract 号：

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

但是我从上面的代码得到的错误是：

Run-time error '91': Object variable With block variable not set.

  • 如何修复此代码，以便返回我正在寻找的值的行号？

  • 使用VBA是否有更有效的方法来查找单元格值？例如，如果我有很多工作表，我想搜索所有工作表并返回值的特定行号和工作表位置 .

非常感谢！

excel vba excel-vba excel-2007

1 回答

  • 1

    WIPrng3在哪里定义？它被定义为公共吗？问题是，当你运行“find”时，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

    需要注意的两件事：如果WIPrng3返回多列范围，MATCH将失败并出现1004错误 . MATCH仅适用于单个列或行 . 在上面的示例中，我将WIPrng3限制为MATCH函数中的第一列以避免这种情况 . 你的代码中没有这个 .

    另一件事是你正在寻找文本字符串“545499”，而不是数字545499.如果您的范围包含数字而不是字符串，您将收到错误 . 您可以使用On Error语句捕获该错误并进行适当处理 .

    最后，我没有看到定义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

    您可能最终会查看更大范围，但不会明显影响性能 .

    我在此示例中添加了On Error，因此您可以看到它是如何工作的 . 在测试之前不要将On Error放在那里，因为它会掩盖所有其他错误 .

    查尔斯威廉姆斯在寻找效率方面做了一些很好的分析http://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/

    回复于

相关问题