Excel VBA：返回列中第一次出现的单词？最后？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13769375/
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
Excel VBA: Return first occurrence of a word in a column? The last?
提问by KaliMa
Say I have, in an arbitrary column, these values from row 1 down:
假设我在任意列中有从第 1 行开始的这些值：
1 A
2 A
3 A
4 A
5 B
6 B
7 B
8 A
9 A
10 A
I want to be able to say start=5 is the first B and last=7 is the last B. If there are no B's return 0 for both first and last.
我希望能够说 start=5 是第一个 B，last=7 是最后一个 B。如果第一个和最后一个都没有 B 返回 0。
采纳答案by Kevin Pope
Would something like this work for you or do you need two separate functions?
像这样的东西对你有用还是你需要两个单独的功能？
Function findValues(place As String, val As String, rng As Range) As Integer
Dim r As Range
findValues = 0
For Each r In rng
If InStr(r.Value2, val) > 0 Then
findValues = r.Row
If LCase(place) = "first" Then
Exit For
End If
End If
Next
End Function
Use like this:
像这样使用：
Dim rng As Range
Set rng = Range("B1:B10")
Dim i As Integer
i = findValues("first", "B", rng)
i = findValues("last", "B", rng)
Depending on how big of a Range you need to check, this could take a while.
根据您需要检查的范围有多大，这可能需要一段时间。
回答by thdoan
Don't forget that in VBA you still have access to a wealth of built-in Excel functions. Examples (assuming your data is in column 1):
不要忘记，在 VBA 中，您仍然可以访问大量内置的 Excel 函数。示例（假设您的数据在第 1 列中）：
Find the first B...Columns(1).Find(What:="B", LookAt:=xlWhole, MatchCase:=False).Row 'Returns 5
找到第一个 B...Columns(1).Find(What:="B", LookAt:=xlWhole, MatchCase:=False).Row 'Returns 5
Find the last B...Columns(1).Find(What:="B", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row 'Returns 7
找到最后一个B...Columns(1).Find(What:="B", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row 'Returns 7
If B is not found, then an error is returned. We can take advantage of this by using error handling to return 0 if B is not found. Putting it all together...
如果未找到 B，则返回错误。如果没有找到 B，我们可以通过使用错误处理返回 0 来利用这一点。把这一切放在一起...
Sub DoTest()
Dim RowFirst As Integer, _
RowLast As Integer
On Error GoTo ErrorHandler
RowFirst = Columns(1).Find(What:="B", LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False).Row
RowLast = Columns(1).Find(What:="B", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row
Exit Sub
ErrorHandler:
RowFirst = 0
RowLast = 0
End Sub
回答by Dick Kusleika
Here's another way.
这是另一种方式。
Sub FindFirstLast()
Dim vaValues As Variant
Dim vaFilter As Variant
Dim lFirst As Long
Dim lLast As Long
Const sFIND As String = "B"
With Application.WorksheetFunction
'Get a 1-d array from a column
vaValues = .Transpose(Sheet1.Range("A1:A10").Value)
'Use match to get the first instance
lFirst = .Match(sFIND, vaValues, False)
'Filter on the values
vaFilter = Filter(vaValues, sFIND)
'Assumes they're all together
lLast = lFirst + UBound(vaFilter)
End With
Debug.Print lFirst, lLast
End Sub
回答by Jacob Johnston
I've been using Kevin Pope's method in a few applications but found that it would sometimes include stuff I didn't want. Thought I'd share my experience and solution.
我一直在一些应用程序中使用 Kevin Pope 的方法，但发现它有时会包含我不想要的东西。以为我会分享我的经验和解决方案。
Recently I realized that if I had something like this:
最近我意识到，如果我有这样的事情：
Mouser EPM1270GT144I5
Mouser EPM1270GT144I5
Mouser GRM32DR71E106K
Mouser GRM32DR71E106K
TTI GRM32DR71E106KA12L
Avnet GS816273CC-250I
Avnet GS816273CC-250I
and was looking for this: GRM32DR71E106K
并且正在寻找这个：GRM32DR71E106K
It would return the two I was searching for and the third that starts with the same string.
它将返回我正在搜索的两个和以相同字符串开头的第三个。
I needed to adapt to search for EXACT matches. The fix was pretty easy:
我需要适应搜索完全匹配。修复非常简单：
Public Function findValues(Val As String, Rng As Range, Optional place As Integer) As Integer
Dim R As Range
findValues = 0
For Each R In Rng
If StrComp(R.Value2, Val) = 0 Then
' If InStr(R.Value2, Val) > 0 Then
findValues = R.Row
If place = 1 Then
Exit For
End If
End If
Next
End Function
I hope someone finds that useful.
我希望有人觉得这很有用。
