VBA Excel 匹配或查找子字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18530641/
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
VBA Excel match or find substring
提问by karamell
Quick example:
快速示例：
A B C D E F
1
2 str1 str2 str3 str4
I want to store the first range-object, in row 2, with stras the first three characters of the cell.
我想将第一个范围对象存储在第 2 行，str作为单元格的前三个字符。
Is this possible in VBA?
这在VBA中可能吗？
This is what I am trying to achieve:
这就是我想要实现的目标：
Dim rng As Range
Dim colNr as integer
colNr = WorksheetFunction.Match(left(cell.value,3)="str", .range("2:2"), 0)
set rng = Sheets("Sheet1").cells(2,colNr)
采纳答案by chris neilsen
You are pretty close:
你很接近：
Dim rng As Range
Dim colNr as Long
With Sheets("Sheet1")
colNr = WorksheetFunction.Match("str*", .range("2:2"), 0)
set rng = .cells(2,colNr)
End With
回答by MikeD
Fundamentally I would use a For Each ... In ... Nextloop and interrupt on first find. Here's a working draft without too much error checking (TargetRange is only 1 row, TargetCell is a string, UCase/LCase, etc ...):
从根本上说，我会使用For Each ... In ... Next循环并在第一次查找时中断。这是一个没有太多错误检查的工作草案（TargetRange 只有 1 行，TargetCell 是一个字符串，UCase/LCase 等...）：
Function FirstMatch(TargetRange As Range, SearchString As String) As Variant
Dim TargetCell As Range
' if we don't find any, we return a #N/A error value
FirstMatch = CVErr(xlErrNA)
For Each TargetCell In TargetRange.Cells
If InStr(1, TargetCell, SearchString) Then
' returning the value of first find
' FirstMatch = TargetCell
' returning the column position relative to the first cell in selected range
FirstMatch = TargetCell.Column - TargetRange(1, 1).Column + 1
Exit Function
End If
Next TargetCell
End Function
usage for your case would be:
您的情况的用法是：
A2: =FirstMatch(C2:F2, "str")
A2： =FirstMatch(C2:F2, "str")
Modify the If ...condition as per your need (e.g Left(...)etc.)
If ...根据您的需要修改条件（例如Left(...)等）
Using a function you avoid hard coding (bad bad bad)!
使用函数可以避免硬编码（坏坏坏）！
A word of caution: Allthough For Each ... In ... Nextdoesn't guarantee a strictly sequential processingI always found it rather safe when used with 1-dimentional ranges of moderate sizes. If you want to be absolutely sure you need to introduce an index variable and use a plain For ... Nextloop.
一个警告：For Each ... In ... Next虽然不能保证严格的顺序处理，但我总是发现它在与中等大小的一维范围一起使用时相当安全。如果你想绝对确定你需要引入一个索引变量并使用一个简单的For ... Next循环。
