查找匹配值的行号 [英] Find row number of matching value

问题描述

我一直在尝试几种不同的方法来查找 bingo 的行号(用星号列出并分隔)，但似乎没有一种有效.我究竟做错了什么?在所有情况下，我都试图同时寻找宾果游戏和宾果游戏".

I've been trying several different methods of finding the row number of bingo (listed and separated by asterisks) but none seem to work. What am I doing wrong? In all instances I've tried looking both for Bingo and "Bingo".

Sub Find_Bingo()

Dim wb As Workbook
Dim ws As Worksheet
Dim FoundCell As Range
Set wb = ActiveWorkbook
Set ws = ActiveSheet

    Const WHAT_TO_FIND As String = "Bingo"

    Set FoundCell = ws.Range("A").Find(What:=WHAT_TO_FIND)
    If Not FoundCell Is Nothing Then
        MsgBox (WHAT_TO_FIND & " found in row: " & FoundCell.Row)
    Else
        MsgBox (WHAT_TO_FIND & " not found")
    End If

'************

    With Sheet1
        Set FoundCell = Cells.Find(What:=Bingo, After:=.Cells(1, 1), _
 LookIn:=xlValues, lookat:= xlPart, SearchOrder:=xlByRows, _ 
 SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    End With

'************

Set FoundCell = Sheets("Sheet1").Columns("E").Find(Bingo, _
ActiveSheet.Cells(2, 2), LookIn:=xlValue, lookat:=xlWhole)

'************

FoundCell = Range("A:M").Find(Bingo)

'************

FoundCell = Application.WorksheetFunction.Match(Bingo, Range("A1:A200"), 0)

'************

FoundCell = Worksheets("Sheet1").Columns(1).Find(Bingo).Row

'************

Range("A:A").Find(Bingo, Range("A1")).Row

'************

ActiveWorkbook.Worksheets("Sheet1").Columns(1).Find(Bingo).Select

'************
End Sub

推荐答案

对于第一种方法，将 ws.Range("A")更改为 ws.Range("A:A")，它将搜索整个a列，如下所示:

For your first method change ws.Range("A") to ws.Range("A:A") which will search the entirety of column a, like so:

Sub Find_Bingo()

        Dim wb As Workbook
        Dim ws As Worksheet
        Dim FoundCell As Range
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet

            Const WHAT_TO_FIND As String = "Bingo"

            Set FoundCell = ws.Range("A:A").Find(What:=WHAT_TO_FIND)
            If Not FoundCell Is Nothing Then
                MsgBox (WHAT_TO_FIND & " found in row: " & FoundCell.Row)
            Else
                MsgBox (WHAT_TO_FIND & " not found")
            End If
End Sub

对于第二种方法，您将 Bingo 用作变量而不是字符串文字.这是一个很好的例子，说明了为什么要在所有代码模块的顶部添加 Option Explicit ，因为当您尝试运行代码时，它将把您定向到未定义且未定义的变量"打算完全是一个变量.

For your second method, you are using Bingo as a variable instead of a string literal. This is a good example of why I add Option Explicit to the top of all of my code modules, as when you try to run the code it will direct you to this "variable" which is undefined and not intended to be a variable at all.

另外，当您使用 With ... End With 时，需要一个句点.在引用 Cells 之前，因此单元格应为 .Cells .这模仿了正常的排位行为(即Sheet1.Cells.Find ..)

Additionally, when you are using With...End With you need a period . before you reference Cells, so Cells should be .Cells. This mimics the normal qualifying behavior (i.e. Sheet1.Cells.Find..)

将 Bingo 更改为"Bingo" ，并将 Cells 更改为 .Cells

Change Bingo to "Bingo" and change Cells to .Cells

With Sheet1
        Set FoundCell = .Cells.Find(What:="Bingo", 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)
Else
        MsgBox ("Bingo not found")
End If

更新

在我的

With Sheet1
    .....
End With

Sheet1 是指工作表的代码名称，而不是工作表本身的名称.例如，说我打开一个新的空白Excel工作簿.默认工作表为 Sheet1 .我可以使用代号为 Sheet1 的代码来引用它，也可以使用索引为 Sheets("Sheet1")的索引来引用它.使用代号的优点是，如果您更改工作表的名称，它不会更改.

The Sheet1 refers to a worksheet's code name, not the name of the worksheet itself. For example, say I open a new blank Excel workbook. The default worksheet is just Sheet1. I can refer to that in code either with the code name of Sheet1 or I can refer to it with the index of Sheets("Sheet1"). The advantage to using a codename is that it does not change if you change the name of the worksheet.

继续此示例，假设我将 Sheet1 重命名为 Data .使用 Sheet1 将继续工作，因为代号名称不变，但是现在使用 Sheets("Sheet1")将返回错误，并且必须将语法更新为工作表的新名称，因此需要为 Sheets("Data").

Continuing this example, let's say I renamed Sheet1 to Data. Using Sheet1 would continue to work, as the code name doesn't change, but now using Sheets("Sheet1") would return an error and that syntax must be updated to the new name of the sheet, so it would need to be Sheets("Data").

在VB编辑器中，您会看到以下内容:

In the VB Editor you would see something like this:

请注意，即使我将名称更改为 Data ，如何也要在左侧保留 Sheet1 .这就是我的代号意思.

Notice how, even though I changed the name to Data, there is still a Sheet1 to the left. That is what I mean by codename.

可以通过两种方式引用 Data 工作表:

The Data worksheet can be referenced in two ways:

Debug.Print Sheet1.Name
Debug.Print Sheets("Data").Name

两者都应返回 Data

可以在此处找到有关工作表代码名称的更多讨论.

More discussion on worksheet code names can be found here.

这篇关于查找匹配值的行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！