vba 查找匹配值的行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32190029/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find row number of matching value
提问by Cats and Dogs
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".
我一直在尝试几种不同的方法来查找bingo（列出并用星号分隔）的行号，但似乎都没有奏效。我究竟做错了什么？在所有情况下，我都尝试寻找 Bingo 和“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
回答by Soulfire
For your first method change ws.Range("A")to ws.Range("A:A")which will search the entirety of column a, like so:
对于你的第一种方法的变化ws.Range("A")，以ws.Range("A:A")将搜索列的全部，就像这样：
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
For your second method, you are using Bingoas a variable instead of a string literal. This is a good example of why I add Option Explicitto 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.
对于您的第二种方法，您使用的Bingo是变量而不是字符串文字。这是一个很好的例子，说明为什么我将添加Option Explicit到所有代码模块的顶部，因为当您尝试运行代码时，它会将您定向到这个未定义且根本不打算成为变量的“变量”。
Additionally, when you are using With...End Withyou need a period .before you reference Cells, so Cellsshould be .Cells. This mimics the normal qualifying behavior (i.e. Sheet1.Cells.Find..)
此外，当您使用时，在引用 之前With...End With需要一个句点，因此应该是。这模仿了正常的排位赛行为（即 Sheet1.Cells.Find..）.CellsCells.Cells
Change Bingoto "Bingo"and change Cellsto .Cells
更改Bingo为"Bingo"和更改Cells为.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
Update
更新
In my
在我的
With Sheet1
.....
End With
The Sheet1refers 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 Sheet1or 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指工作表的代号，工作表本身不是名称。例如，假设我打开一个新的空白 Excel 工作簿。默认工作表只是Sheet1. 我指的是在代码或者与代码名称Sheet1或者我可以参照它的索引Sheets("Sheet1")。使用代号的优点是如果您更改工作表的名称，它不会更改。
Continuing this example, let's say I renamed Sheet1to Data. Using Sheet1would 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").
继续这个例子，假设我重命名Sheet1为Data. UsingSheet1会继续工作，因为代码名称不会改变，但现在 usingSheets("Sheet1")会返回一个错误，并且该语法必须更新为工作表的新名称，因此它需要是Sheets("Data").
In the VB Editor you would see something like this:
在 VB 编辑器中，您会看到如下内容：
Notice how, even though I changed the name to Data, there is still a Sheet1to the left. That is what I mean by codename.
请注意，即使我将名称更改为Data，Sheet1左侧仍然有一个。这就是我所说的代号。
The Dataworksheet can be referenced in two ways:
该Data工作表可以通过两种方式进行引用：
Debug.Print Sheet1.Name
Debug.Print Sheets("Data").Name
Both should return Data
两者都应该返回 Data
More discussion on worksheet code names can be found here.
可以在此处找到有关工作表代码名称的更多讨论。
