vba 如何查找单元格是否在特定位置包含特定字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18493165/
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
How to find if cell contains specific characters in specific positions?
提问by user2726121
I'm trying to build a macro that will search a given cell for a certain character combination in a specific order, and then paste this to another page. I need it to search a cell that contains a 9 letter/number combination for '9P', but only if it's the third and fourth number/letter in.
我正在尝试构建一个宏，它将以特定顺序搜索给定单元格中的某个字符组合，然后将其粘贴到另一个页面。我需要它来搜索包含“9P”的 9 个字母/数字组合的单元格，但前提是它是其中的第三个和第四个数字/字母。
So if given these list of entries:
因此，如果给出这些条目列表：
NA9PK99LJ
NA9PK99LK
XX9P109LH
XX9P109XF
XX849P01D
NA8419PZ3
XX9P109VK
I'd only want it to copy the first four and the last entry, and then past these vertically starting in A2 on another page.
我只希望它复制前四个和最后一个条目，然后在另一页上从 A2 开始垂直越过这些条目。
I'm somewhat of a novice to excel vba, but I'm told this should be doable.
我是一个擅长 vba 的新手，但有人告诉我这应该是可行的。
Any help would be much appreciated!
任何帮助将非常感激！
Thanks
谢谢
Chris
克里斯
回答by Rob White
Hope this helps, may have gone over board
希望这会有所帮助，可能已经过火了
Sub Solution()
Dim search As String, start As Integer, lastaddress As String, toworksheet As String
lastaddress = "A2" 'cell location on the result sheet
search = InputBox("Enter Search Critera") 'enter search critera
start = InputBox("Start from") 'integer of where to search in the string, not zero index
toworksheet = InputBox("Put results into which spreadsheet") 'worksheet name to put results
'select the cell you want to start your search from and it will continue till it reaches a blank cell
Do While ActiveCell.Text <> ""
'Performs the test
If Mid(ActiveCell.Text, start, Len(search)) = search Then
'adds the entry to the results sheet
Worksheets(toworksheet).Cells.Range(lastaddress).Value = ActiveCell.Text
'updates the address to the next line in your results sheet
lastaddress = Worksheets(toworksheet).Cells.Range(lastaddress).Offset(1, 0).Address
End If
'goes to next item in list
ActiveCell.Offset(1, 0).Select
Loop
End Sub
回答by ApplePie
The easiest way I know of is to use Find() with wildcards. What you can do is use the macro recorder while doing the following:
我所知道的最简单的方法是使用 Find() 和通配符。您可以做的是在执行以下操作时使用宏记录器：
- Start the macro recorder
- Select the range to search
- Do Ctrl + F and enter
??9P?????into the search terms - Select all the results in the box
- Close the box, copy the cells and paste them where you want
- Stop the macro recorder
- 启动宏记录器
- 选择要搜索的范围
- 做 Ctrl + F 并输入
??9P?????搜索词 - 选择框中的所有结果
- 关闭框，复制单元格并将它们粘贴到您想要的位置
- 停止宏记录器
This will give you the main base of your code which you can then fine tune so that it does what you need. This is also a good exercice for you to learn how VBA works.
这将为您提供代码的主要基础，然后您可以对其进行微调，使其满足您的需求。这也是一个很好的练习，可以让您了解 VBA 的工作原理。
