I want to regex replace the cell in one column 3 times, which A ---> 10, B ---> 11, C ---> 12
I know how to use the regex for once, but I need to use the code for 3 times in the vba, it seems so tedious. But I haven't found a easier way yet. Any better solution here?
Set RegExp = CreateObject("vbscript.regexp")
RegExp.Pattern = "B"
Set SearchRange = ActiveSheet.Range("A4:A100")
For Each Cell In SearchRange
Set Matches = RegExp.Execute(Cell.Value)
If Matches.Count >= 1 Then
Set Match = Matches(0)
Cell.Value = RegExp.Replace(Cell.Value, "11")
End If
Next
Update tnx a lot for your reply ,
I now use call sub
Sub Main()
Call RegExp_Replace("A259", "JustTest", "key")
End Sub
Private Sub RegExp_Replace(OpRange, MatchStr, ReplaceStr)
Dim RegExp As Object
Dim SearchRange As Range, Cell As Range
'此处定义正则表达式
Set RegExp = CreateObject("vbscript.regexp")
RegExp.Pattern = MatchStr
'此处指定查找范围
Set SearchRange = ActiveSheet.Range(OpRange)
'遍历查找范围内的单元格
For Each Cell In SearchRange
Set Matches = RegExp.Execute(Cell.Value)
If Matches.Count >= 1 Then
Set Match = Matches(0)
Cell.Value = RegExp.Replace(Cell.Value, ReplaceStr)
End If
Next
End Sub
Bookmarks