|
我发一个VBA 灵活性代码 可以作为工具使用- Sub 正则替换()
-
-
- On Error Resume Next
- Set regx = CreateObject("vbscript.regexp")
- 1:
- Set sjq = Application.InputBox(prompt:="请选择:查找区域", Type:=8)
- If sjq Is Nothing Then GoTo ff1
- sjqzhs = sjq.Rows.Count
- sjqqsl = sjq.Column
- sjqqsh = sjq.Row
- Set jgq = Application.InputBox(prompt:="请选择:结果摆放起始位", Type:=8)
- If jgq Is Nothing Then GoTo ff1
- Application.ScreenUpdating = False
- jgqqsh = jgq.Row
- jgql = jgq.Column
- arr = sjq
- With regx
- .Global = True '默认为 false 只查找第一个 True则全部查找
- bds = Application.InputBox(prompt:="请输入:正则表达式")
- .Pattern = bds 'Pattern 是属性 正则表达式的内容
- bb = MsgBox("是全部替换还是保留 是为替换 否为保留", vbYesNo)
- If bb = 7 Then GoTo 11
- th = Application.InputBox(prompt:="替换为:什么")
- zcljzf = Application.InputBox(prompt:="请输入最初连接字符:什么")
- jwljzf = Application.InputBox(prompt:="请输入最末连接字符:什么")
- If sjqzhs > 1 Then GoTo 2
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = zcljzf & .Replace(arr, th)
- GoTo ff
- 2:
- For Each Rng In arr
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = zcljzf & .Replace(Rng, th) & jwljzf 'Replace为一种方法 此种方法为替换
- If Cells(jgqqsh + n, jgql) = zcljzf & jwljzf Then Cells(jgqqsh + n, jgql) = ""
- If zcljzf & Cells(sjqqsh + n, sjqqsl) & jwljzf = Cells(jgqqsh + n, jgql) Then Cells(jgqqsh + n, jgql).NumberFormatLocal = "@": Cells(jgqqsh + n, jgql) = Cells(sjqqsh + n, sjqqsl)
- n = n + 1
- Next
- GoTo ff
- 11:
- bbb = MsgBox("是保留全部还是一个 是为全部 否为一个", vbYesNo)
- If bbb = 7 Then GoTo 12
- If sjqzhs > 1 Then GoTo 3
- fgf = Application.InputBox(prompt:="请输入:分隔符")
- Set matc = .Execute(arr)
- sl = matc.Count
- For Each mm In matc
- kk = kk + 1
- If kk = sl Then GoTo 15
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = mm2 & mm & fgf
- mm2 = mm2 & mm & fgf
- GoTo 17
- 15:
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = mm2 & mm
- 17:
- Next mm
- GoTo ff
- 3:
- fgf = Application.InputBox(prompt:="请输入:分隔符")
- For Each m1 In arr
- Set k = regx.Execute(m1) 'Execute方法:返回匹配成功的结果,是一个对象
- js = k.Count
- For Each m In k
- xhcs = xhcs + 1
- If js = 1 Then GoTo 33
- If js > 1 And xhcs = 1 Then GoTo 33
- If xhcs = js Then GoTo 35
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = m2 & m & fgf
- m2 = m2 & m & fgf
- GoTo 34
- 33:
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = m
- m2 = m & fgf
- GoTo 34
- 35:
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = m2 & m
- 34:
- Next
- m2 = ""
- xhcs = 0
- n = n + 1
- Next
- GoTo ff
- 12:
- If sjqzhs > 1 Then GoTo 4
- fgf = Application.InputBox(prompt:="请输入:分隔符")
- Set matc = .Execute(arr)
- sl = matc.Count
- For Each mm In matc
- kk = kk + 1
- If kk = sl Then GoTo 5
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = mm2 & mm & fgf
- mm2 = mm2 & mm & fgf
- GoTo 7
- 5:
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = mm2 & mm
- 7:
- Next mm
- End With
- GoTo ff
- 4:
- For Each m1 In arr
- Set k = regx.Execute(m1)
- For Each m In k
- ii = ii + 1
- If ii > 1 Then GoTo 36
- Cells(jgqqsh + n, jgql).NumberFormatLocal = "@"
- Cells(jgqqsh + n, jgql) = m
- Next
- 36:
- ii = 0
- n = n + 1
- Next
-
- ff:
- n = 0
- Application.ScreenUpdating = True
- aa = MsgBox("是否还需继续处理", vbYesNo)
- If aa = 6 Then GoTo 1
- ff1:
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|