VBA¦p¦ó¤ñ¹ïÀx¦s®æ¯S©w¦r¦ê
- ©«¤l
- 25
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 56
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2002
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-12-18
- ³Ì«áµn¿ý
- 2022-12-6
|
VBA¦p¦ó¤ñ¹ïÀx¦s®æ¯S©w¦r¦ê
½Ð°ÝVBA¦p¦ó±NB2~B27Äæ¦r¦ê¨Ì§Ç¼Ò½k¤ñ¹ïGÄæ¦r¦ê¡A¦³²Å¦X«h½Æ¨î¦r¦ê¶K¤WCÄæ¡AÁÂÁ¤j¤j¸Ñµª
|
|
marklu
|
|
|
|
|
- ©«¤l
- 976
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 1018
- ÂI¦W
- 0
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-4-19
- ³Ì«áµn¿ý
- 2024-10-30
|
¦^´_ 1# lumark1976
½Ð°Ý³o¦³¤ñ¸û©ú½T³W«h¶Ü?
1. ¦]¬°shielding n§äshiel ©Î shieldi ©Î shildin .... ¡A
2. SCREW(M3*6) n§ä SCREW M3 ©Î SCREW M3*6 ....¡A |
|
|
|
|
|
|
- ©«¤l
- 25
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 56
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2002
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-12-18
- ³Ì«áµn¿ý
- 2022-12-6
|
¥»©«³Ì«á¥Ñ 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
|
|
|
|
|
- ©«¤l
- 976
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 1018
- ÂI¦W
- 0
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-4-19
- ³Ì«áµn¿ý
- 2024-10-30
|
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 |
|
|
|
|
|
|
- ©«¤l
- 25
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 56
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2002
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-12-18
- ³Ì«áµn¿ý
- 2022-12-6
|
·PÁ¤j¤j¡A¥i¥H¤F¡A¦A¦¸·PÁ |
|
marklu
|
|
|
|
|
- ©«¤l
- 25
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 56
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2002
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-12-18
- ³Ì«áµn¿ý
- 2022-12-6
|
¦^´_ 4# samwang
¤j¤j¡A§Ú¬Oªì¾ÇªÌ¡A¥i¥Hµù¸Ñ»¡©ú¤@¤U§A¼gªºµ{¦¡½X¶Ü¡A·P¿E¤£ºÉ¡I¡I:) |
|
marklu
|
|
|
|
|
- ©«¤l
- 976
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 1018
- ÂI¦W
- 0
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-4-19
- ³Ì«áµn¿ý
- 2024-10-30
|
¦^´_ 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 |
|
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¦^´_ 1# lumark1976
ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
³oºØ²£«~ªº±ÔznÂkÃþ¶W³Â·Ð!
½Ð«e½ú¸Õ¸Õ¬Ý!
´N¦¹½d¨Ò½m²ß°}¦C»P¦r¨å
°õ¦æµ²ªG:
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 |
|
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
ÁÂÁ½׾Â!ÁÂÁ«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 |
|
|
|
|
|
|
- ©«¤l
- 25
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 56
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2002
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-12-18
- ³Ì«áµn¿ý
- 2022-12-6
|
¦^´_ 9# Andy2483
:) «z¡A¯uªº¤Ó´Î¤F¡A¯uªº¤Ó·PÁ±z¤F¡I¡I |
|
marklu
|
|
|
|
|