根据单元格值查找行的功能 [英] Function to find row based on cell value

问题描述

我有以下代码来查找特定值所在的行，但是它会不断使用

I have the following code to find the row where a certain value resides, however it keeps debugging with

错误91未设置对象变量或With块变量"

Error 91 "Object variable or With block variable not set"

这很奇怪，因为在此过程之前，我使用相同的结构来查找行，并且行得通.

Which is weird because I use the same structure to find a row before this procedure and it works.

wbs.Activate
    Cells.Find(What:="Name", After:=wbs.Range("A1"), LookIn:=xlFormulas, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=True, SearchFormat:=False).Activate
    NameRow = ActiveCell.Row

推荐答案

您唯一的问题是，当工作表上没有名称"时.查找会返回 Nothing 而不是 Range 对象.然后，由于尝试在 Nothing 上使用 .Activate .

Your only problem is that when you don't have "Name" on your worksheet .Find returns Nothing rather than a Range object. You then get an error because you are trying to use .Activate on Nothing.

解决方案

The solution

不需要使用 Activate 和 ActiveCell ，只需定义好变量并使用它们即可！这是一个工作示例:

There is no need to use Activate and ActiveCell, just define your variables well and use them! Here's a working example:

Sub test()
    Dim wks As Worksheet
    Dim r As Range
    Dim rowNumber As Long

    Set wks = ThisWorkbook.Worksheets("sheet1") 'update for your worksheet name

    '.Find returns a Range object or Nothing    
    Set r = wks.Cells.Find(What:="Name", LookAt:=xlWhole) 

    If Not r Is Nothing Then
        rowNumber = r.Row
    End If

    MsgBox rowNumber
End Sub

这篇关于根据单元格值查找行的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！