vba 用于从单元格中删除不需要的字符的宏循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27387477/
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
Macro loop for deleting unwanted characters from cells
提问by XDHEU35
I have data in columns A and B. I need a macro that will loop through these columns and delete the text at the end. The columns look like this:
我在 A 列和 B 列中有数据。我需要一个宏来遍历这些列并在最后删除文本。列如下所示：
Column A Column B
1234(AZ) X1258Q156(ABCD)
3259(AZ) 81R58Q1V6(ABCD)
7849(AZ) X1X58-156(ABCD)
1489(AZ) XX258Q1(ABCD)
I need the macro to loop through column A and column B and remove the characters at the end. In column A's example, remove all (AZ) from it and in column B's example remove all (ABCD) from it. The text length will vary in column A and column B. Thank you.
我需要宏循环遍历 A 列和 B 列并删除末尾的字符。在 A 列的示例中，从中删除所有 (AZ)，在 B 列的示例中，从中删除所有 (ABCD)。A 列和 B 列中的文本长度会有所不同。谢谢。
采纳答案by Chrismas007
You'll have to write the loop and insert the value into the range, but this will do what you want within the loop:
您必须编写循环并将值插入范围，但这将在循环中执行您想要的操作：
Range().Value = Left(Range().Value, InStr(1, Range().Value, Chr(40)) - 1)
Edit: Left()makes more sense...
编辑：Left()更有意义...
Edit2: Find/Replace Method:
Edit2：查找/替换方法：
Range(ENTIRE RANGE HERE).Replace("(*)", "", LookAt:=xlPart)
回答by bilbo_strikes_back
Dim str1 As String
Dim str2 As String
Dim rngTemp As Range
Dim rngCell As Range
str1 = "(AZ)"
str2 = "(ABCD)"
'Set rngTemp
Set rngTemp = Cells(1, 1).CurrentRegion 'You range goes here
'Loop through range and replace string
For Each rngCell In rngTemp
If InStr(1, rngCell, str1) > 0 Then
rngCell = Replace(rngCell.Value, str1, "")
End If
If InStr(1, rngCell, str2) > 0 Then
rngCell = Replace(rngCell.Value, str2, "")
End If
Next rngCell
回答by nattro
This code should work for you. Assuming your code has the column you are working on and the text you want to remove.
这段代码应该适合你。假设您的代码包含您正在处理的列和要删除的文本。
Sub YourSub()
RemoveCharactersFromComumn "A", "(AZ)"
RemoveCharactersFromComumn "B", "(ABCD)"
End Sub
Sub RemoveCharactersFromComumn(col As String, find As String)
Dim rng As Range
Set rng = Range(col + ":" + col)
rng.Replace What:=find, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
