|
嗯,你表里有些首行标题跟总表对应首行标题有些出入,要改成一样的 ,像这个标题(报价单产品收费启示日),在三个表里面各有各名称,
Sub xxx()
Dim x As Long, y As Long, st As String, brr() As String, d, arr, crr() As String, l1 As Long, l2 As Long
Set d = CreateObject("scripting.dictionary")
arr = Sheets("表1").UsedRange: arr1 = Sheets("上海").UsedRange: arr2 = Sheets("外地").UsedRange
ReDim brr(1 To 10000, 1 To UBound(arr1, 2)): ReDim crr(1 To 10000, 1 To UBound(arr2, 2))
l1 = UBound(arr1) + 1: l2 = UBound(arr2) + 1
For x = 1 To UBound(arr, 2) '创建表标题字典
If arr(1, x) <> "" Then d(arr(1, x)) = x
Next
For x = 1 To UBound(arr1, 2)
If arr1(1, x) <> "" Then d(arr1(1, x) & 1) = x
Next
For x = 1 To UBound(arr2, 2)
If arr2(1, x) <> "" Then d(arr2(1, x) & 2) = x
Next
For x = 2 To UBound(arr) '查找总表表头标题在其他表位置,有放入数据,无跳过
If arr(x, d("城市名称")) = "上海" Then
k1 = k1 + 1
For y = 1 To UBound(arr, 2)
If d.exists(arr(1, y) & 1) Then brr(k1, d(arr(1, y) & 1)) = arr(x, y)
Next
Else
k2 = k2 + 1
For y = 1 To UBound(arr, 2)
If d.exists(arr(1, y) & 2) Then crr(k2, d(arr(1, y) & 2)) = arr(x, y)
Next
End If
Next
With CreateObject("VBScript.RegExp") '用正则表达式提取匹配标注里的数据
.Global = True
Sheets("上海").Range("a" & l1 + 1).Resize(k1, UBound(arr1, 2)) = brr
For Each rg In Sheets("上海").Cells.SpecialCells(xlCellTypeComments)
For x = l1 To k1 + l1
If rg.Offset(x, 0) = "" Then GoTo 10
.Pattern = "\d+(?=[^1-9]?" & rg.Offset(x, 0) & ")"
If .test(rg.Comment.Text) = False Then GoTo 10
rg.Offset(x, 0) = .Execute(rg.Comment.Text)(0)
10 Next
Next
Sheets("外地").Range("a" & l2 + 1).Resize(k2, UBound(arr2, 2)) = crr
For Each rg In Sheets("外地").Cells.SpecialCells(xlCellTypeComments)
For x = l2 To k2 + l2
.Pattern = "\d+(?=[^1-9]?" & rg.Offset(x, 0) & ")"
If .test(rg.Comment.Text) = False Or rg.Offset(x, 0) = "" Then GoTo 11
rg.Offset(x, 0) = .Execute(rg.Comment.Text)(0)
11 Next
Next
End With
End Sub
|
|