- ©«¤l
- 1018
- ¥DÃD
- 15
- ºëµØ
- 0
- ¿n¤À
- 1058
- ÂI¦W
- 0
- §@·~¨t²Î
- win7 32bit
- ³nÅ骩¥»
- Office 2016 64-bit
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ®ç¶é
- µù¥U®É¶¡
- 2012-5-9
- ³Ì«áµn¿ý
- 2022-9-28
|
¦^´_ 4# v03586
Try this
4.¨Ì·ÓDÄæ¬Û¦Pªº¦r¦ê¥Î¶Â®Ø½u => §ï¬°¨Ì¦X¨ÖªºAÄæµe²Ê®Ø- Sub Test()
- Dim oRegexp As Object: Set oRegexp = CreateObject("vbscript.regexp")
- Dim ar, i, j, s
-
- ar = Sheets("³øªí").Range("A9:F21").Value 'or .Range("A9").CurrentRegion.Value
-
- With oRegexp
- .Pattern = "^[^-]*-"
- For i = 1 To UBound(ar)
- If .Test(ar(i, 3)) Then ar(i, 3) = .Replace(ar(i, 3), "")
- Next
- .Pattern = "^[^-]*-[^-]*-"
- For i = 1 To UBound(ar)
- If .Test(ar(i, 4)) Then ar(i, 4) = .Replace(ar(i, 4), "")
- Next
- .Pattern = "^(.{2})(.)(.*)[a-zA-Z]$"
- For i = 1 To UBound(ar)
- If .Test(ar(i, 5)) Then
- s = .Replace(ar(i, 5), "$1-$2-$3")
- mch = Application.VLookup(s, Sheets("Flow").[A:B], 2, False)
- If Not IsError(mch) Then ar(i, 5) = mch
- End If
- Next
- .Pattern = "(SPC|SCL).*$"
- '¥t¤@ºØ QVS to SPC/SCL
- '.Pattern = "^.*?(QVS.*?(SPC|SCL)).*$"
- For i = 1 To UBound(ar)
- If .Test(ar(i, 6)) Then ar(i, 6) = .Replace(ar(i, 6), "$1")
- Next
-
- End With
-
- Application.ScreenUpdating = False
- With Sheets.Add.[A1].Resize(UBound(ar), UBound(ar, 2))
- .Value = ar
- .Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 4), order2:=xlAscending
- .Borders.LineStyle = xlContinuous
- .Borders.Weight = xlThin
- Application.DisplayAlerts = False
- i = 1
- For j = 1 To UBound(ar)
- If .Cells(j, 2).Value <> .Cells(j + 1, 2).Value Then
- If i <> j Then Range(.Cells(i, 1), .Cells(j, 1)).Merge
- With .Cells(i, 1).MergeArea.Resize(, .Columns.Count)
- .Borders(xlEdgeTop).Weight = xlMedium
- .Borders(xlEdgeBottom).Weight = xlMedium
- .Borders(xlEdgeLeft).Weight = xlMedium
- .Borders(xlEdgeRight).Weight = xlMedium
- End With
- i = j + 1
- End If
- Next
- Application.DisplayAlerts = True
-
- [B:B].Font.Size = 16
- [C:D].Font.Size = 12
- ActiveWindow.Zoom = 63
- [A:F].EntireColumn.AutoFit
- [A:B].HorizontalAlignment = xlCenter
- End With
- Application.ScreenUpdating = True
- End Sub
½Æ»s¥N½X |
|