如何通过 vba 代码 Cells.Find 在 excel 列中查找值

How to find a value in an excel column by vba code Cells.Find

提问by Jonathan Raul Tapia Lopez

I have to find a value celdain an Excel sheet. I was using this vba code to find it:

我必须在 Excel 工作表中找到一个值celda。我正在使用此 vba 代码来查找它：

Set cell = Cells.Find(What:=celda, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)


If cell Is Nothing Then
    'do it something

Else
    'do it another thing
End If

The problem is when I have to find the value only in a excel column. I find it with next code:

问题是当我必须仅在 excel 列中查找值时。我用下一个代码找到它：

    Columns("B:B").Select
    Selection.Find(What:="VA22GU1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

But I don't know how to adapt it to the first vba code, because I have to use the value nothing.

但是我不知道如何使它适应第一个 vba 代码，因为我必须使用 value nothing。

回答by Andrey Gordeev

Just use

只需使用

Dim Cell As Range
Columns("B:B").Select
Set cell = Selection.Find(What:="celda", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then
    'do it something

Else
    'do it another thing
End If

回答by Mário Meyrelles

Just for sake of completeness, you can also use the same technique above with excel tables.

为了完整起见，您还可以对 Excel 表格使用上述相同的技术。

In the example below, I'm looking of a text in any cell of a Excel Table named "tblConfig", place in the sheet named Config that normally is set to be hidden. I'm accepting the defaults of the Find method.

在下面的示例中，我正在查找名为“tblConfig”的 Excel 表的任何单元格中的文本，并将其放置在名为 Config 的工作表中，该工作表通常设置为隐藏。我接受 Find 方法的默认值。

Dim list As ListObject
Dim config As Worksheet
Dim cell as Range


Set config = Sheets("Config")
Set list = config.ListObjects("tblConfig")

'search in any cell of the data range of excel table
Set cell = list.DataBodyRange.Find(searchTerm)

If cell Is Nothing Then
    'when information is not found
Else
    'when information is found
End If

回答by robartsd

I'd prefer to use the .Findmethod directly on a range object containing the range of cells to be searched. For original poster's code it might look like:

我更喜欢.Find直接在包含要搜索的单元格范围的范围对象上使用该方法。对于原始海报的代码，它可能如下所示：

Set cell = ActiveSheet.Columns("B:B").Find( _
    What:=celda, _
    After:=ActiveCell _
    LookIn:=xlFormulas, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=False _
)

If cell Is Nothing Then
    'do something
Else
    'do something else
End If

I'd prefer to use more variables (and be sure to declare them) and let a lot of optional arguments use their default values:

我更喜欢使用更多变量（并确保声明它们）并让许多可选参数使用它们的默认值：

Dim rng as Range
Dim cell as Range
Dim search as String

Set rng = ActiveSheet.Columns("B:B")
search = "String to Find"
Set cell = rng.Find(What:=search, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

If cell Is Nothing Then
    'do something
Else
    'do something else
End If

I kept LookIn:=, LookAt::=, and MatchCase:=to be explicit about what is being matched. The other optional parameters control the order matches are returned in - I'd only specify those if the order is important to my application.

我保留LookIn:=, LookAt::=, 并MatchCase:=明确说明正在匹配的内容。其他可选参数控制返回的订单匹配 - 如果订单对我的应用程序很重要，我只会指定那些。

回答by Mona

Dim strFirstAddress As String
Dim searchlast As Range
Dim search As Range

Set search = ActiveSheet.Range("A1:A100")
Set searchlast = search.Cells(search.Cells.Count)

Set rngFindValue = ActiveSheet.Range("A1:A100").Find(Text, searchlast, xlValues)
If Not rngFindValue Is Nothing Then
  strFirstAddress = rngFindValue.Address
  Do
    Set rngFindValue = search.FindNext(rngFindValue)
  Loop Until rngFindValue.Address = strFirstAddress