ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¸ê®Æ­åªR+¥[®Ø½u

[µo°Ý] ¸ê®Æ­åªR+¥[®Ø½u

½Ð°Ý¦U¦ì°ª¤â¤j¤j, ¦p¦ó±N³o¬q¥\¯àÂରµ{¦¡½X
1.  ¦³ÂIÃþ¦ü¸ê®Æ­åªRªº·§©À, ¦]¬°®æ¦¡¤£¤@©w , ¦ý³W«h½T©wªº¬O§R°£±q¥ªÃä¼Æ¹L¨Ó¨â­Ó¡y-¡z
     «O¯d¬õ¦âªº¦rÅé

2.  µe½u«á§PÂ_¤£¦Pªº¥[²ÊÅé¤À¿ë





TEST.rar (8.18 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-7-29 09:58 ½s¿è

¦^´_ 1# v03586
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim E As Range, sp As Variant, i As Integer, Msg As Boolean, St As String
  4.     St = ",AAP,PPA,QQP,POO,"   '¥½½X³W«h
  5.     With Range("h2").CurrentRegion
  6.     'Range.CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪªº¡C
  7.         For Each E In .Cells
  8.            sp = Split(E.Value, "-")
  9.            'Split ¨ç¼Æ ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C , ¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê
  10.             Msg = InStr(St, "," & UCase(UBound(sp)) & ",")
  11.             For i = 0 To UBound(sp) - IIf(Msg, 2, 1)
  12.                 E = Replace(E, sp(i) & "-", "")
  13.                 'Replace ¨ç¼Æ ¶Ç¦^¤@­Ó¦r¦ê , ¸Ó¦r¦ê¤¤«ü©wªº¤l¦r¦ê¤w³Q¨ú¥N¦¨¥t¤@¤l¦r¦ê, ¨Ã¥B¨ú¥Nµo¥Íªº¦¸¼Æ¤]¬O«ü©wªº
  14.             Next
  15.         Next
  16.         .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=xlAutomatic
  17.         For Each E In .Cells
  18.             
  19.              With E.Borders(9)  'xlInsideVertical
  20.                 .LineStyle = xlContinuous
  21.                 .ColorIndex = xlAutomatic
  22.                 .TintAndShade = 0
  23.                 .Weight = IIf(E.Offset(1) <> E, xlThick, xlMedium)
  24.             End With
  25.         Next
  26.     End With
  27. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2017/7/29 10:02): 2#µ{¦¡½X¤w§ó·s , ¦A¸Õ¸Õ¬Ý

¥»©«³Ì«á¥Ñ v03586 ©ó 2017-7-29 09:37 ½s¿è

¦^´_ 2# GBKEE


    ·PÁª©¤jªº¤ä´©,
    ·Q°Ý¤@¤U, ¥Lªº¥½½X³W«h¤£¤@©w¬OQQP ªº¸Ü, ¤]¥i¯à¬O§OªºAAP ¤§Ãþªº , ¤£©T©w, ¬OÁÙ­n¦b¥[¤@¦ê
  1. For i = 0 To UBound(sp) - IIf(InStr(E, "QQP"), 2, 1) and UBound(sp) - IIf(InStr(E, "AAP"), 2, 1)
½Æ»s¥N½X
³o¼Ë¶Ü?? ½T©w©T©w³W«h¼Ò¦¡±q¥ªÃä¼Æ¨Ó¨â­Ó¡y-¡z¬O­n§R°£ªº

TOP

¥»©«³Ì«á¥Ñ v03586 ©ó 2017-7-29 10:28 ½s¿è

¦^´_ 2# GBKEE


    ªO¤j¥[¤J¤F¥½½X³W«h«á, µ{¦¡¶]§¹Åܦ¨ ¥u«O¯d¥½½X, ¡yAAP , QQP ¡z
    ¤£¬OÅܦ¨«O¯d¹³¹Ï¤ù¤¤«O¯d¬õ¦â¦rÅ鳡¤À QQ
    §Ú«öF8 ¥h¶]  ·|Åܦ¨¤U­±³o¬q §â¥LÅܦ¨ AAP , QQP
  1. For i = 0 To UBound(sp) - IIf(Msg, 2, 1)
  2.                 E = Replace(E, sp(i) & "-", "")
  3.                 'Replace ¨ç¼Æ ¶Ç¦^¤@­Ó¦r¦ê , ¸Ó¦r¦ê¤¤«ü©wªº¤l¦r¦ê¤w³Q¨ú¥N¦¨¥t¤@¤l¦r¦ê, ¨Ã¥B¨ú¥Nµo¥Íªº¦¸¼Æ¤]¬O«ü©wªº
  4.             Next
½Æ»s¥N½X

TOP

¦^´_ 4# v03586

¦A¤W¶ÇÀɮ׬ݬÝ
2#µ{¦¡½X °õ¦æ«á¦p¹Ï

·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ v03586 ©ó 2017-7-29 12:56 ½s¿è

¦^´_ 5# GBKEE

   ¦p¹Ï¤ù»¡©ú , °ß¤@§äªº¨ì¤£Åܪº³W«h, ´N¬O±q¥ªÃ䩹¥kºâ¨â­Ó¡y-¡z ¤£«O¯d
    ¦pªG±q¥kÃäºâ, ¦³ªº·|¦³¥½½XQQP µ¥¦r½X
    Q2.png

     ¦³ªº¦r¦ê·|¦³¤T­Ó¡y-¡z  e.g.
     XX-XXXX-XXXX-QQP
     ¦³ªº¦r¦ê¥u¦³¨â­Ó¡y-¡z e.g.
    XX-XXXX-XXXX
    ­n«O¯dªº ¨Ì´`¦³®Ä³W«ß³W«h, ±q¥ªÃ䩹¥kºâ2­Ó¡y-¡z


TEST.rar (16.76 KB)

TOP

¦^´_ 6# v03586

­×§ï¤F, ¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim E As Range, sp As Variant, i As Integer
  4.     With Range("h2").CurrentRegion
  5.     'Range.CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪªº¡C
  6.         For Each E In .Cells
  7.            sp = Split(E.Value, "-")
  8.            'Split ¨ç¼Æ ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C , ¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê
  9.             If UBound(sp) > 2 Then   ' ³W«h, ¦³²Ä¤G­Ó¡y-¡z,¤~³B¸Ì
  10.                 For i = 0 To IIf(UBound(sp) > 2, UBound(sp) - 2, 1)
  11.                     E = Replace(E, sp(i) & "-", "")
  12.                 'Replace ¨ç¼Æ ¶Ç¦^¤@­Ó¦r¦ê , ¸Ó¦r¦ê¤¤«ü©wªº¤l¦r¦ê¤w³Q¨ú¥N¦¨¥t¤@¤l¦r¦ê, ¨Ã¥B¨ú¥Nµo¥Íªº¦¸¼Æ¤]¬O«ü©wªº
  13.                 Next
  14.             End If
  15.         Next
  16.         .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=xlAutomatic
  17.         For Each E In .Cells
  18.              With E.Borders(9)  'xlInsideVertical
  19.                 .LineStyle = xlContinuous
  20.                 .ColorIndex = xlAutomatic
  21.                 .TintAndShade = 0
  22.                 .Weight = IIf(E.Offset(1) <> E, xlThick, xlMedium)
  23.             End With
  24.         Next
  25.     End With
  26. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 6# v03586
¥tºØ¸Ñªk¡A½Ð°Ñ¦Ò¡C
TEST-1.rar (22.02 KB)

TOP

TT = "09-SAF5-1111K-QQP"
TT = Split(Replace(TT, "-", "+", , 2), "+")(2)
MsgBox TT

TT = "09-C231-100WK"
TT = Split(Replace(TT, "-", "+", , 2), "+")(2)
MsgBox TT

TOP

  1. Sub ex()
  2. Dim A As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. For Each A In Range("H2:H15")
  5.   st = Split(Application.WorksheetFunction.Substitute(A, "-", Chr(10), 2), Chr(10))(1)
  6.   A.Offset(, 1) = st '¼g¤J
  7.   If IsEmpty(d(st)) Then '¦P¤å¦rÀx¦s®æ½d³ò¼È¦s
  8.     Set d(st) = A.Offset(, 1)
  9.     Else
  10.     Set d(st) = Union(d(st), A.Offset(, 1))
  11.   End If
  12. Next
  13. For Each ky In d.keys
  14. For i = 7 To 12
  15. With d(ky).Borders(i) 'µe®Ø½u
  16.         .LineStyle = xlContinuous
  17.         .ColorIndex = 0
  18.         .TintAndShade = 0
  19.         .Weight = IIf(i > 10, xlThin, xlMedium)
  20. End With
  21. Next
  22. Next
  23. End Sub
½Æ»s¥N½X
¦^´_ 6# v03586
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : «Î¼e¤£¦p¤ß¼e¡C
ªð¦^¦Cªí ¤W¤@¥DÃD