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

VBA¦p¦ó¤ñ¹ïÀx¦s®æ¯S©w¦r¦ê

VBA¦p¦ó¤ñ¹ïÀx¦s®æ¯S©w¦r¦ê

½Ð°ÝVBA¦p¦ó±NB2~B27Äæ¦r¦ê¨Ì§Ç¼Ò½k¤ñ¹ïGÄæ¦r¦ê¡A¦³²Å¦X«h½Æ¨î¦r¦ê¶K¤WCÄæ¡AÁÂÁ¤j¤j¸Ñµª
1.png
2022-11-28 17:50

¸ê®Æ®w¤ñ¹ï.zip (19.6 KB)

marklu

¦^´_ 1# lumark1976


½Ð°Ý³o¦³¤ñ¸û©ú½T³W«h¶Ü?
1. ¦]¬°shielding ­n§äshiel ©Î shieldi ©Î shildin .... ¡A
2. SCREW(M3*6) ­n§ä SCREW M3 ©Î SCREW M3*6 ....¡A

TOP

¥»©«³Ì«á¥Ñ lumark1976 ©ó 2022-11-29 08:42 ½s¿è

Sam¤j¤j¡A¦pªG¤ñ¹ï¥X¦³»PGÄæ¤@¼Ëªº¦r¦ê´N¦n¡A¦³¿ìªk°µ¨ì¶Ü¡H·PÁ¡I¡I
marklu

TOP

Sam¤j¤j¡A¦pªG¤ñ¹ï¥X¦³»PGÄæ¤@¼Ëªº¦r¦ê´N¦n¡A¦³¿ìªk°µ¨ì¶Ü¡H·PÁ¡I¡I
lumark1976 µoªí©ó 2022-11-29 08:39


½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr, T$, i&, i2&
Arr = Range([c1], [b65536].End(3))
Brr = Range([g1], [g65536].End(3))
For i = 2 To UBound(Brr)
    T = UCase(Brr(i, 1)): If T = "" Then GoTo 99
    For i2 = 2 To UBound(Arr)
        If InStr(UCase(Arr(i2, 1)), T) Then
            Arr(i2, 2) = Brr(i, 1)
        End If
    Next
99: Next
[b1].Resize(UBound(Arr), 2) = Arr
End Sub

TOP

·PÁ¤j¤j¡A¥i¥H¤F¡A¦A¦¸·PÁÂ
marklu

TOP

¦^´_ 4# samwang

¤j¤j¡A§Ú¬Oªì¾ÇªÌ¡A¥i¥Hµù¸Ñ»¡©ú¤@¤U§A¼gªºµ{¦¡½X¶Ü¡A·P¿E¤£ºÉ¡I¡I:)
marklu

TOP

¦^´_  samwang

¤j¤j¡A§Ú¬Oªì¾ÇªÌ¡A¥i¥Hµù¸Ñ»¡©ú¤@¤U§A¼gªºµ{¦¡½X¶Ü¡A·P¿E¤£ºÉ¡I¡I
lumark1976 µoªí©ó 2022-11-29 09:44



Sub test()
Dim Arr, Brr, T$, i&, i2&
Arr = Range([c1], [b65536].End(3)) 'B¡BCÄæ¸ê®Æ¸Ë¤JArr
Brr = Range([g1], [g65536].End(3)) 'GÄæ¸ê®Æ¸Ë¤JBrr
For i = 2 To UBound(Brr)
    T = UCase(Brr(i, 1)): If T = "" Then GoTo 99 'µL¸ê®Æ´«¤U¤@­Ó
    For i2 = 2 To UBound(Arr)
        If InStr(UCase(Arr(i2, 1)), T) Then '¤ñ¹ïGÄæ¸ê®Æ¦³µL¦bBÄæ
            Arr(i2, 2) = Brr(i, 1)   '¦³®É¡AGÄæ¸ê®Æ¼g¤J¨ìArr
        End If
    Next
99: Next
[b1].Resize(UBound(Arr), 2) = Arr  'Arr¶K¦^excel
End Sub

TOP

¦^´_ 1# lumark1976

ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
³oºØ²£«~ªº±Ô­z­nÂkÃþ¶W³Â·Ð!
½Ð«e½ú¸Õ¸Õ¬Ý!
´N¦¹½d¨Ò½m²ß°}¦C»P¦r¨å
°õ¦æµ²ªG:
2022-11-29_163637.JPG
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

TOP

ÁÂÁ½׾Â!ÁÂÁ«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

TOP

¦^´_ 9# Andy2483


    :) «z¡A¯uªº¤Ó´Î¤F¡A¯uªº¤Ó·PÁ±z¤F¡I¡I
marklu

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD