时间戳

首页 > 解决方案 > 计算使用 Excel 的 Find 方法找到的搜索结果的总数

excel - 计算使用 Excel 的 Find 方法找到的搜索结果的总数

问题描述

是否可以返回使用 Excel 的 find 方法找到的匹配总数？如果是这样，那看起来会如何，或者我将如何计算搜索结果的总数？

到目前为止，这是我想要构建的内容：

Private Sub btnSearch_Click()

    With Sheet1
        Set foundCell = .Cells.Find(What:="B-32", After:=.Cells(1, 1), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    End With

    If Not foundCell Is Nothing Then
            MsgBox ("""Bingo"" found in row " & foundCell.Row)
            UserForm1.location.Text = Cells(foundCell.Row, 3).Value
            UserForm1.office.Value = Cells(foundCell.Row, 2).Value
            UserForm1.floor.Value = Cells(foundCell.Row, 1).Value
            UserForm1.status.Value = Cells(foundCell.Row, 4).Value
            UserForm1.telephone.Value = Cells(foundCell.Row, 5).Value
            UserForm1.mobile.Value = Cells(foundCell.Row, 6).Value
            UserForm1.owner.Value = Cells(foundCell.Row, 7).Value
            UserForm1.notes.Value = Cells(foundCell.Row, 8).Value
    Else
            MsgBox ("Bingo not found")
    End If

End Sub

标签： excelvba

解决方案

统计总搜索次数

你可以使用CountIF()

此外，始终明确地将范围引用限定为所需的工作表

最后，请注意 Mathieu Guindon 的建议

如下：

With Sheet1
    Set foundCell = .Cells.Find(What:="B-32", After:=.Cells(1, 1), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Not foundCell Is Nothing Then
        MsgBox ("""Bingo"" found " & WorksheetFunction.CountIf(.Cells, "*B-32*") & " times")

        MsgBox ("first ""Bingo"" found in row " & foundCell.Row)

        Me.Location.Text = .Cells(foundCell.Row, 3).Value
        Me.Office.Value = .Cells(foundCell.Row, 2).Value
        Me.Floor.Value = .Cells(foundCell.Row, 1).Value
        Me.Status.Value = .Cells(foundCell.Row, 4).Value
        Me.telephone.Value = .Cells(foundCell.Row, 5).Value
        Me.mobile.Value = .Cells(foundCell.Row, 6).Value
        Me.owner.Value = .Cells(foundCell.Row, 7).Value
        Me.Notes.Value = .Cells(foundCell.Row, 8).Value
    Else
        MsgBox ("Bingo not found")
    End If
End With

推荐阅读