[¦r¨åª«¥ó]³æ¤@Äæ¦ìªºÈ»P¦hÓ°}¦C¤ñ¹ï«á¨Ã¤ÀÃþ¦Ü¤£¦PÄæ¦ì
©«¤l 45 ¥DÃD 10 ºëµØ 0 ¿n¤À 59 ÂI¦W 0 §@·~¨t²Î Win7 ³nÅ骩¥» Office 2007 ¾\ŪÅv 20 ©Ê§O ¨k µù¥U®É¶¡ 2014-2-6 ³Ì«áµn¿ý 2019-6-22
[¦r¨åª«¥ó]³æ¤@Äæ¦ìªºÈ»P¦hÓ°}¦C¤ñ¹ï«á¨Ã¤ÀÃþ¦Ü¤£¦PÄæ¦ì
¦U¦ì«e½ú¦n,
¤p§Ì¤§«e¦³¦bºô¤W§ä¨ì¤@Óµ{¦¡½X,
°Ñ¦Òºô§}¦p¤U:
http://club.excelhome.net/thread-868892-1-1.htmlz
¨ä¤¤ªº¹ê¨Ò4,
¥i±N¤@Ä椺ªºÈ¨Ï¥ÎInStr¥h¤ñ¹ï¦hÓ°}¦CªºÈ,
°²³]¦r¦ê¦³²Å¦X,¥i¤ÀÃþ¦Ü¤£¦PÄæ¦ì.
²{¦b¹J¨ìªº²Ä¤@Ó°ÝÃD¬O,
¸Óµ{¦¡½X¬O±N°}¦CªºÈ¼g¦bµ{¦¡¤§¤¤,Ãþ¦ü³o¼Ë:
brr1 = Array("ASUS", "Sony")
¦ý³o¦b¹ê§@¤W¬O¤£¤Ó¥i¯à³o¼Ë¥h¹B¥Îªº,³q±`·|·Qn±NÀx¦s®æ¤ºªºÈ¼g¤J°}¦C¤¤,
¦Ó«D¤@Ó¤@Ó¤â°Ê¥´¦bµ{¦¡½X¤º.
¥i¬O,Y·Q±NÀx¦s®æªºÈ¦s¦Ü°}¦C¤¤,Ãþ¦ü³o¼Ë:
brr1 = Range("j2:j" & Cells(Rows.Count, 10).End(xlUp).Row)
«hµ{¦¡·|¥X²{¿ù»~,¤p§Ì¹ê¦b¤£ª¾¸Ó¦p¦ó¸Ñ¨M.
²Ä¤GÓ°ÝÃD¬O,
Yì©l¸ê®Æ¤ºªºÈ§¹¥þ¨S¦³²Å¦X°}¦CÈ¥ô¤@¦r¦êªº¸Ü,¤]·|¥X²{¿ù»~.
ªþ¤W¤p§Ìקï«áªºµ{¦¡½X¤Îªþ¥ó¦p¤U:
20161020_°ÝÃD-¤ñ¹ï³æ¦æ¸ê®Æ»P¦hÓ°}¦C.zip (11.13 KB)
Sub test1()
'°Ñ¦Òºô§}:http://club.excelhome.net/thread-868892-1-1.htmlz
'¹ê¨Ò4
Dim arr, myD1, myD2, myD3
arr = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
'Y¨Ï¥Î¥H¤U¤è¦¡±NÀx¦s®æ¹ïÀ³¦Ü°}¦C«h·|¥X°ÝÃD
'brr1 = Range("j2:j" & Cells(Rows.Count, 10).End(xlUp).Row)
'brr2 = Range("k2:k" & Cells(Rows.Count, 11).End(xlUp).Row)
'brr3 = Range("L2:L" & Cells(Rows.Count, 12).End(xlUp).Row)
brr1 = Array("ASUS", "Sony")
brr2 = Array("Samsung")
brr3 = Array("ªø¦¿", "¤p¦Ì")
Set myD1 = CreateObject("Scripting.Dictionary")
Set myD2 = CreateObject("Scripting.Dictionary")
Set myD3 = CreateObject("Scripting.Dictionary")
Set myD4 = CreateObject("Scripting.Dictionary")
For x = 1 To UBound(arr)
For i = 0 To UBound(brr1)
If InStr(arr(x, 1), brr1(i)) > 0 Then
myD1(arr(x, 1)) = ""
GoTo 100
End If
Next i
For j = 0 To UBound(brr2)
If InStr(arr(x, 1), brr2(j)) > 0 Then
myD2(arr(x, 1)) = ""
GoTo 100
End If
Next j
For k = 0 To UBound(brr3)
If InStr(arr(x, 1), brr3(k)) > 0 Then
myD3(arr(x, 1)) = ""
GoTo 100
End If
Next k
myD4(arr(x, 1)) = ""
100:
Next x
Range("b2").Resize(UBound(myD1.keys) + 1, 1) = Application.Transpose(myD1.keys)
Range("c2").Resize(UBound(myD2.keys) + 1, 1) = Application.Transpose(myD2.keys)
Range("d2").Resize(UBound(myD3.keys) + 1, 1) = Application.Transpose(myD3.keys)
Range("e2").Resize(UBound(myD4.keys) + 1, 1) = Application.Transpose(myD4.keys)
End Sub ½Æ»s¥N½X Y¥i¥Hªº¸Ü§Æ±æ«e½ú¯à°÷¤£§[«üÂI°g¬z,¤Q¤À·PÁÂ.
©«¤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
¦^´_ 9# ã´£³¡ªL
ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß«e½ú¤è®×2(¤£«½Æ),¤è®×¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É
°õ¦æµ²ªG:
Sub U_Test()
Dim xR As Range, xD, Arr, Brr, Mx&, N%, G(1 To 4), DK
'¡ô«Å§iÅܼÆ
[B2:E200].ClearContents
'¡ô²M°£µ²ªGÄæ¸ê®Æ
Arr = Range([A2], Cells(Rows.Count, 1).End(xlUp))
'¡ô¥OArrÅܼƬO¤Gºû°}¦C,¥HAÄæÀx¦s®æÈ(ì©l¸ê®Æ)±a¤J°}¦C¸Ì
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxDÅܼƬO¦r¨å
For Each xR In [J2:L40]
'¡ô¥O³]³v¶µ°j°é!¥OxR¬O½d³òÀx¦s®æ¸Ìªº¤@®æ
If xR <> "" Then xD(UCase(xR)) = xR.Column - 9
'¡ô¦pªGxRÅܼƤ£¬OªÅªº!´N¥O¨äÂà´«¬°¤j¼g^¤å·íkey,item¬O¨äÄæ¼Æ-9,
'¯Ç¤JxD¦r¨å¸Ì('ÃöÁä¦r¨Ì¨äÄæ¦ì±a§Ç¸¹)
Next
ReDim Brr(1 To UBound(Arr), 1 To 4)
'¡ô«Å§iBrrÅܼƬO¤GºûªÅ°}¦C,Áa¦V½d³ò¦PArr,¾î¦V±q1 ¨ì4
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é
N = 4
'¡ô¥ONÅܼƬO 4 ('¹w³]§Ç¸¹¬°4,¬O¥Î¨Ó©ñ¤£²Å¦Xªº¸ê®Æ)
For Each DK In xD.keys
'¡ô³]³v¶µ°j°é!¥ODK¬OxD¦r¨å¸Ìªº¤@Ókey
If InStr(UCase(Arr(i, 1)), DK) Then N = xD(DK): Exit For
'¡ô¦pªGì©l¸ê®ÆÂà´«^¤å¤j¼gªº·s¦r¦ê¸Ì¦³¥]§tDKÅܼÆ!
'´N¥ONÅܼÆÅÜ§ó¬° ¥HDKÅܼƬdxD¦r¨åªºitemÈ('¦³²Å¦X,¨ú¥X§Ç¸¹),
'¨ú¥X§Ç¸¹«á´Nµ²§ô°j°é,¥Nªí¤£«½Æ¨Ï¥Î ì©l¸ê®Æ
Next
G(N) = G(N) + 1
'¡ô¨Ì§Ç¸¹¤£¦P, ¦U¦Û¦bG³o¤@ºû°}¦C¸Ì ²ÖpÄæ¦ìªºµ§¼Æ
If G(N) > Mx Then Mx = G(N)
'¡ô¨ú±o³Ì¤jµ§¼Æ
Brr(G(N), N) = Arr(i, 1)
'¡ô«ö§Ç¸¹¤Îµ§¼Æ¶ñ¤J¸ê®Æ¨ì°}¦C
Next i
[B2].Resize(Mx, 4) = Brr
'¡ô¥O±q[B2]¶}©lÂX®i¦³¸ê®Æªº¦C¼Æ4Äæ,¥HBrr°}¦Cȱa¤J
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
©«¤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
¦^´_ 9# ã´£³¡ªL
ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß«e½úªº¤è®×1,¤è®×¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É
°õ¦æ«e:
°õ¦æµ²ªG:
Sub ex()
[B2:E200].ClearContents
'¡ô²M°£µ²ªGÄæ¸ê®Æ
Set d = CreateObject("Scripting.Dictionary")
'¡ô¥OdÅܼƬO¦r¨å
Set Rng = Range("J1").CurrentRegion.SpecialCells(xlCellTypeConstants)
'¡ô¥ORngÅܼƬO [J1]¦ê¨ÃÁpÀx¦s®æÂX®i½d³òªº«DªÅ¥Õ®æ ('¤ñ¹ï°}¦C)
For Each a In Range([A2], [A2].End(xlDown))
'¡ô³]³v¶µ°j°é!¥OaÅܼƬOAÄæ¸ÌªºÀx¦s®æ ('ì©l¸ê®Æ°j°é)
For Each c In Rng
'¡ô³]³v¶µ°j°é!¥OcÅܼƬORngÅܼƸ̪ºÀx¦s®æ
If InStr(UCase(a), UCase(c)) > 0 Then
'¡ô¦pªGaÅܼÆÀx¦s®æÈÂà´«¦¨^¤å¤j¼gªº·s¦r¦ê«á,
'¸Ì±¦³¥]§t cÅܼÆÀx¦s®æÈÂà´«¦¨^¤å¤j¼gªº·s¦r¦ê
d(c.Column) = ""
'¡ô¥O¥HcÅܼÆÄæ¦ì¼Æ·íkey,item¬OªÅ¦r¤¸,¯Ç¤Jd¦r¨å¸Ì
'('°O¦í¤ñ¹ï¨ì°}¦CªºÄæ¦ì)
End If
Next
If d.Count > 0 Then
'¡ô¦pªGd¦r¨åkey¼Æ¶q>0 ?('ªí¥Üì©l¸ê®Æ¤ñ¹ï¦¨¥\)
For Each ky In d.keys
'¡ô³]³v¶µ°j°é!¥Oky¬Od¦r¨å¸Ìªº¤@Ókey
Cells(65536, ky - 8).End(xlUp).Offset(1, 0) = a
'¡ô¥Oµ²ªGÄ檺²Ä¤@Ӫťծæ¬O aÅܼÆ(Àx¦s®æÈ)
Next
d.RemoveAll
'¡ô²MªÅd¦r¨å
Else
Cells(65536, "E").End(xlUp).Offset(1, 0) = a
'¡ô¤ñ¹ï¤£¦¨¥\!´N±NaÅܼÆ(Àx¦s®æÈ)©ñ¦bEÄæ²Ä¤@Ӫťծæ
End If
Next
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
©«¤l 45 ¥DÃD 10 ºëµØ 0 ¿n¤À 59 ÂI¦W 0 §@·~¨t²Î Win7 ³nÅ骩¥» Office 2007 ¾\ŪÅv 20 ©Ê§O ¨k µù¥U®É¶¡ 2014-2-6 ³Ì«áµn¿ý 2019-6-22
¦^´_ 11# ã´£³¡ªL
¤F¸Ñ,¦³¬Ý¨ì¤F,¬O¼g¦b¤u§@ªí¤º,¤£¦n·N«ä,¤p§Ì¦A¬ã¨s¤@¤U.·PÁ«e½ú´£¿ô.
©«¤l 2831 ¥DÃD 10 ºëµØ 0 ¿n¤À 2887 ÂI¦W 0 §@·~¨t²Î ¡e²¤¡f ³nÅ骩¥» ¡e²¤¡f ¾\ŪÅv 100 ©Ê§O ¨k ¨Ó¦Û ¡e²¤¡f µù¥U®É¶¡ 2013-5-13 ³Ì«áµn¿ý 2024-11-5
¥»©«³Ì«á¥Ñ ã´£³¡ªL ©ó 2016-10-21 19:53 ½s¿è
¦^´_ 10# greetingsfromtw
¨º¬O¤u§@ªí¡e¨Æ¥ó¡fIJµoµ{¦¡¡A«öAlt + F11¡A¹ï¤u§@ªíª«¥ó«ö¨â¤U
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim xR As Range
With Target
¡@¡@If Intersect([J2:L8], .Cells) Is Nothing Then Exit Sub
¡@¡@If .Value = "" Then Exit Sub
¡@¡@Cancel = True
¡@¡@For Each xR In Range([A2], Cells(Rows.Count, 1).End(xlUp))
¡@¡@¡@¡@If InStr(UCase(xR), UCase(.Value)) > 0 Then
¡@¡@¡@¡@¡@Cells(Rows.Count, "G").End(xlUp)(2) = xR
¡@¡@¡@¡@End If
¡@¡@Next
End With
End Sub
©«¤l 45 ¥DÃD 10 ºëµØ 0 ¿n¤À 59 ÂI¦W 0 §@·~¨t²Î Win7 ³nÅ骩¥» Office 2007 ¾\ŪÅv 20 ©Ê§O ¨k µù¥U®É¶¡ 2014-2-6 ³Ì«áµn¿ý 2019-6-22
¦^´_ 9# ã´£³¡ªL
«D±`ÁÂÁ²a´£³¡ªL«e½úªº¦^À³.
ÁöµM¥»¨°^Ämªº¤£¬O«Ü¦h,´N¬O§Æ±æ¯à°÷¹ï½×¾Â¦³¨Ç·LÀ°§U.
¤p§Ì¦³±N¤T¦ì«e½úªº¤è¦¡³£¦³µy·L´ú¸Õ,»¡¯uªº,ÁöµM¤p§Ì³}¹Lªº¬ÛÃö½×¾Â«Ü¤Ö,
¦ý«e½ú̪ºµ{¦¡½X©M§Ú¦b¨ä¥L¦a¤è¬Ý¨ìªº¼gªk¥i»¡¬O§¹¥þ¤£¦P.¬Û·íÅå¤H,ȱo¬ã¨s.
¦]¥Ø«e¥»¨ªºVBAµ{«×¤Ó®t,©Ò¥H²{¦b¥i¯à¬O¸ÕµÛ¼Ò¥é«e½úªº¼gªk,
¥ý¥h¼g¤@¨Ç°ò¥»ªº¥\¯à³o¼Ë,ÁöµM¬O²Â¤èªk,¤£¹L°_¨BÁ`¬OµhWªº.
¥u¯à»¡¥ú¥Î¬Ýªº¸ò¦Û¤v¼g¬O§¹¥þ¨â¦^¨Æªü....ÁÙ±o¦A§V¤O.
¥t¥~¤]¤Q¤À·PÁ«e½ú©Ò´£¨ÑªºªþÀÉ,
·Æ¹«ÂI¨â¤Uª½±µ¿z¿ïªº³oÓ¥\¯à¹ê¦b¤Ó²r¤F,«e©Ò¥¼¨£,
¤p§Ì¤æÁx,¤£ª¾¬O§_¤è«K³Â·Ð«e½ú«üÂI¬O«ç»ò°µ¨ìªº?
¦n¹³¤£¬O¼g¦b¥¨¶°¤¤,¤]¨S¦³¨Ï¥ÎÀx¦s®æ¤½¦¡.
©«¤l 2831 ¥DÃD 10 ºëµØ 0 ¿n¤À 2887 ÂI¦W 0 §@·~¨t²Î ¡e²¤¡f ³nÅ骩¥» ¡e²¤¡f ¾\ŪÅv 100 ©Ê§O ¨k ¨Ó¦Û ¡e²¤¡f µù¥U®É¶¡ 2013-5-13 ³Ì«áµn¿ý 2024-11-5
¦^´_ 8# greetingsfromtw
¢°¡D¯àÃÙ§U§Y¥i¶Q¡A³o¤]À°§U¥L¤H¥i¥H¦b¦¹Àò±oª¾ÃÑ
¢±¡D¢³¼Ó¶WªOªº¤èªk¤]³\¸û²Å¹ê±¡¡]¥H¿z¿ï¦Ó¨¥¡^¡A¦P¤@¤å¦r¥]§t¨âÓÃöÁä¦r®É¡A«h¨â¦¸¤À§O¨ú¥X¡A
¡@¡@¥i´ú¸Õ½d¨ÒÀɪº¨âÓµ{¦¡¥h¤ñ¸û
¢²¡D¥t´£¨Ñ¤@Ó¤è«K°w¹ï¿ï¨ú³æ¤@ÃöÁä¦rªº¿z¿ïªk
20161020_¤ñ¹ï³æ¦æ¸ê®Æ»P¦hÓ°}¦Cv01.rar (14.18 KB)
¡@
¡@
©«¤l 45 ¥DÃD 10 ºëµØ 0 ¿n¤À 59 ÂI¦W 0 §@·~¨t²Î Win7 ³nÅ骩¥» Office 2007 ¾\ŪÅv 20 ©Ê§O ¨k µù¥U®É¶¡ 2014-2-6 ³Ì«áµn¿ý 2019-6-22
¦^´_ 5# GBKEE
«D±`·PÁÂGBKEE«e½úÄ@·N¥Î¨ä¥L¤è¦¡¨Ó´£¨Ñ¦¹°ÝÃDªº¸Ñªk,
ì©lªºµ{¦¡½X¸û¬°¤¾ªø,
¦ý¤T¦ì«e½ú§¡»´´y²H¼g¦a¥Î¤Ö¶qµ{¦¡½X¼g¥Xº}«G¸Ñµª,
¦¹½×¾Â¹ê¦b¬Oª×ªêÂÃÀs,¦³³\¦hexcel°ª¤â«e½ú«LÁô©ó¦¹,
¦Ó¥BÁÙÄ@·NµL¨p«üÂI¹³·s¤â,¤p§Ì»Ê·P¤¤º.
Æg½à¤§¨¥»¡±o¹L¦h¤]¾á¤ß¦³¯B¸Ø¤§¶û,
¤p§Ì¦¬¤J¤£¬O¤Ó¦h,©Ò¥H¥i¯à¨S¿ìªk«ùÄòÃÙ§U°ªÃB¶O¥Î,
¦ýÄ@·N¥H"¤ÖÃBÃÙ§U"»P"§V¤O¾Ç²ß",
¨Ó¤À¾á¯¸ªø¤j¤HºûÅ@¦¹½×¾Âªº³]³Æ¶O¥Î»P¦^À³ªO¤W«e½ú̵L¨p«üÂIªº®¦¼w.
¦A¦¸·PÁÂ.
©«¤l 192 ¥DÃD 15 ºëµØ 0 ¿n¤À 194 ÂI¦W 0 §@·~¨t²Î windows ³nÅ骩¥» office2010 ¾\ŪÅv 20 ©Ê§O ¤k µù¥U®É¶¡ 2016-9-22 ³Ì«áµn¿ý 2020-8-28
[ª©¥DºÞ²z¯d¨¥] GBKEE(2016/10/21 12:26): ¤ñ¹ïªº¸Ü¥i¥H¥Î¦bListBox¸Ì±¶Ü,ªþÀɬݬÝ
¦^´_ 4# Hsieh
¤ñ¹ïªº¸Ü¥i¥H¥Î¦bListBox¸Ì±¶Ü
µ¥©ó§Ú¤Ï¥Õ¸Ì±¨ä¤¤1¶µ¸ê°T
Åý¥L¹ïÀ³«á¥h°µ¿z¿ï
³o¬O¥i¦æªº¶Ü
©«¤l 45 ¥DÃD 10 ºëµØ 0 ¿n¤À 59 ÂI¦W 0 §@·~¨t²Î Win7 ³nÅ骩¥» Office 2007 ¾\ŪÅv 20 ©Ê§O ¨k µù¥U®É¶¡ 2014-2-6 ³Ì«áµn¿ý 2019-6-22
¦^´_ 4# Hsieh
«D±`·PÁÂHsieh«e½ú¯S¦a´£¨Ñ¦r¨åª«¥óªº¥tÃþ¸Ñªk,
¤]¬O±Nìµ{¦¡½XÁY´î¤@¤j³¡¥÷,¥O¤H¨ØªA.
¦¹½×¾Â¯u¬O°ª¤â¶³¶°,¤p§Ì¤@©w§V¤O¾Ç²ß.