Andy2483

¦^´_ 1# lumark1976

ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
³oºØ²£«~ªº±Ô­z­nÂkÃþ¶W³Â·Ð!
½Ð«e½ú¸Õ¸Õ¬Ý!
´N¦¹½d¨Ò½m²ß°}¦C»P¦r¨å
°õ¦æµ²ªG:

¤U¸ü (320.53 KB)

2022-11-29 16:42

Option Explicit
Sub TEST_2()
Dim Brr, Grr, Crr, C&, i&, x$, xR, R&, T, V, Y, Z
Dim Sh, Q$(5), A$(5), j&
Set Y = CreateObject("Scripting.Dictionary")
Set Sh = Sheets("PN¼Ð·Ç¤u®É")
Brr = Range(Sh.[B2], Sh.Cells(Rows.Count, "B").End(3))
Grr = Range(Sh.[G2], Sh.Cells(Rows.Count, "G").End(3))
ReDim Crr(1 To UBound(Brr), 1 To 1)
For i = 1 To UBound(Grr)
   xR = Replace(Replace(Replace(UCase(Grr(i, 1)), "(", "*"), ")", "*"), " ", "*")
   Y(xR) = Grr(i, 1)
Next
Q(1) = UCase("CONN POWER JACK")
A(1) = UCase("POWER JACK")
Q(2) = UCase("CONN RJ45")
A(2) = UCase("RJ45")
Q(3) = UCase("Shield")
A(3) = UCase("Shielding")
For Each xR In Y.Keys
   For i = 1 To UBound(Brr)
      If Crr(i, 1) <> "" Then
         GoTo 111
      End If
      x = UCase(Brr(i, 1))
      For j = 1 To 3
         x = Replace(x, Q(j), A(j))
      Next
      If x Like "*" & xR & "*" Then
         Crr(i, 1) = Y(xR)
      End If
      
111
   Next
Next
Sh.[C2].Resize(UBound(Crr), 1) = Crr
Set Brr = Nothing
Set Grr = Nothing
End Sub

Andy2483

ÁÂÁ½׾Â!ÁÂÁ«e½ú­Ì
§Æ±æ¯àÀ°¦£¨ì¼Ó¥D«e½ú!«á¾Ç¦b¦¹¥DÃD¾Ç²ß¨ì«Ü¦hª¾ÃÑ!
¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú­Ì¦A«ü¾É!ÁÂÁÂ

Option Explicit
Sub TEST_2()
Dim Brr, Grr, Crr, C&, i&, x$, xR, R&, T, V, Y, Z
Dim Sh, Q$(5), A$(5), j&
'¡ô«Å§iÅܼÆ,Q$(5)¬O§å¦¸«Å§i±qQ(0)~Q(5), A$(5)Ãþ±À
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY¬O¦r¨å
Set Sh = Sheets("PN¼Ð·Ç¤u®É")
'¡ô¥OSh ¬O¤u§@ªí
Brr = Range(Sh.[B2], Sh.Cells(Rows.Count, "B").End(3))
'¡ô¥OBrr ¬O°}¦C!­Ë¤J¤u§@ªí[B2]¨ìBÄæ³Ì«á¦³¤º®eªºÀx¦s®æªº­È
Grr = Range(Sh.[G2], Sh.Cells(Rows.Count, "G").End(3))
'¡ô¥OGrr ¬O°}¦C!­Ë¤J¤u§@ªí[G2]¨ìGÄæ³Ì«á¦³¤º®eªºÀx¦s®æªº­È
ReDim Crr(1 To UBound(Brr), 1 To 1)
'¡ô«Å§iCrr°}¦Cªº½d³ò!Áa¦V±q1¨ìBrr°}¦CÁa¦V³Ì¤j¦C¸¹,¾î¦V±q1¨ì1Äæ
For i = 1 To UBound(Grr)
'¡ô³]¶¶°j°é!i±q1¨ì Grr°}¦CÁa¦V³Ì¤j¦C¸¹
   xR = Replace(Replace(Replace(UCase(Grr(i, 1)), "(", "*"), ")", "*"), " ", "*")
   '¡ô¥OxR ¬O°j°éGrr°}¦C­È¸g ­^¤å¦rÂà¤j¼g¦A±N "(" ,")" ," ",³o¤T­Ó¦r¤¸¸m´«¬°"*"¦r¤¸ªº¦r¦ê·s­È
   Y(xR) = Grr(i, 1)
   '¡ô¥OxR ·íkey,°j°éGrr°}¦C­È·íitem
Next
Q(1) = UCase("CONN POWER JACK")
'¡ô¥O²Ä¤G­ÓQ¦r¦êÅܼƬO ±NÂù¤Þ¸¹¤º¤å¦rªº­^¤å¦r¸m´«¬°¤j¼g«áªº·s¦r¦ê
A(1) = UCase("POWER JACK")
'¡ô¥O²Ä¤G­ÓA¦r¦êÅܼƬO ±NÂù¤Þ¸¹¤º¤å¦rªº­^¤å¦r¸m´«¬°¤j¼g«áªº·s¦r¦ê
Q(2) = UCase("CONN RJ45")
'¡ôÃþ±À
A(2) = UCase("RJ45")
'¡ôÃþ±À
Q(3) = UCase("Shield")
'¡ôÃþ±À
A(3) = UCase("Shielding")
'¡ôÃþ±À
For Each xR In Y.Keys
'¡ô³]¥~¶¶°j°é!¥OxR¬O Y¦r¨å¸Ìkeyªº¤@­û
   For i = 1 To UBound(Brr)
   '¡ô³]¤¤¶¶°j°é!i±q1 ¨ìBrr°}¦CÁa¦V³Ì¤j¦C¸¹
      If Crr(i, 1) <> "" Then
      '¡ô¦pªG°j°éCrr°}¦C­È¤£¬OªÅ¦r¤¸
         GoTo 111
         '¡ô¸õ¨ì 111¦ì¸mÄ~Äò°õ¦æ,¥Nªí¤w¸g³B²z¹L!¦³­È¤F
      End If
      x = UCase(Brr(i, 1))
      '¡ô¥Ox¬O °j°éBrr°}¦C­È¸g ­^¤å¦r¸m´«¬°¤j¼g«áªº·s¦r¦ê
      For j = 1 To 3
      '¡ô³]¤º¶¶°j°é!j±q1 ¨ì3
         x = Replace(x, Q(j), A(j))
         '¡ô¥Ox¬O x¸g¹LQ¦r¦êÅܼƸm´«¬° A¦r¦êÅܼƪº·s¦r¦ê
      Next
      If x Like "*" & xR & "*" Then
      '¡ô¦pªG¸g¹L³B²z¹Lªº x·s¦r¦ê §k¦X xR³okeyµ²¦X«e«á¥]§¨¤@­Ó*¸U¦r¤¸ªºÅÞ¿è§PÂ_??
         Crr(i, 1) = Y(xR)
         '¡ô¦pªGif±ø¥ó¦¨¥ß!´NÅý°j°éCrr°}¦C­È¥H xR¦r¦êÅܼƷíkey¬d¹îY¦r¨åªºitem±a¤J
      End If
      
111
   Next
Next
Sh.[C2].Resize(UBound(Crr), 1) = Crr
'¡ô¤u§@ªíªº[C2]Àx¦s®æÂX®i¦V¤U Crr°}¦CÁa¦V³Ì¤j¦C¸¹¼Æ,¥ª¥k¨S¦AÂX®iªºÀx¦s®æ½d³ò,
'­Ë¤JCrr°}¦Cªº­È
Set Brr = Nothing
Set Grr = Nothing
Set Crr = Nothing
Set Y = Nothing
'¡ô§â³o¨Ç®e¾¹±q°O¾ÐÅ餤ÄÀ©ñ±¼
End Sub