VB中做一个模板
模板里:有两个工作表,一个是数据表,另外一个是效果我用分列,转置,再用函数,有点麻烦。我想试试另外一种VB模式,水平有限Sub 拆分Sp()
Dim regEx As Object, match As Object
Dim S值 As String, strInput As String, i As Integer, 效果表row As Long, j累 As Integer, 数据表row As Long
S值 = "(\w+)\((\d+)\)|(\w+)"
Set regEx = CreateObject("VBScript.RegExp")
Set 数据表 = Sheets("数据表")
Set 效果表 = Sheets("效果")
效果表row = 效果表.Cells(Rows.Count, 1).End(xlUp).Row + 1
数据表row = 效果表.Cells(Rows.Count, 1).End(xlUp).Row + 1
效果表.Range("A2:B" & 效果表row).ClearContents
'循环处理数据表
For i = 2 To 数据表row
j累 = 效果表.Cells(Rows.Count, 1).End(xlUp).Row + 1
strInput = Sheets("数据表").Range("A" & i).Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = S值
End With
'循环处理匹配结果
For Each match In regEx.Execute(strInput)
效果表.Range("A" & j累).Value = match.SubMatches(0)
效果表.Range("B" & j累).Value = match.SubMatches(1)
j累 = j累 + 1
Next match
Next i
Set regEx = Nothing
Set 数据表 = Nothing
Set 效果表 = Nothing
End Sub
页:
[1]