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

[µo°Ý] ¿z¿ï?ÃöÁä¦r?¬d¸ß?

¦^´_ 20# GBKEE


    GBKEEª©¥D¡A§Ú¤w¸g¦³¸Õ¹L¤F(¥u¬O§Ú¬O¥Î¤â°Êªº¤è¦¡¸Õ)¡A©Ò¥H¤~·|³o»ò¸ß°Ý¡A
   §Ú·Q°Ýªº¬O¡A¯à¥Î¥¨¶°¼g¤â°Ê¥h°µªº³o¨Ç°Ê§@¶Ü?
    ÁÙ¬O¥u¯à¤â°Ê¤~¯àÅý¿z¿ïªºÁôÂÃÀx¦s®æ"¥þ³¡"²MªÅ?¾Þ§@¤â°Ê°Ê§@ªº¥¨¶°¬O¦s¦bªº¶Ü?ÁÂÁÂ^^

§Ú¤â°Êªº¤è¦¡¬O±N
Zip CodeÄæ¦ìªº¿z¿ï§ï¦^¡u¥þ³¡¡v
Scope   Äæ¦ìªº¿z¿ï§ï¦^¡u¥þ³¡¡v
¦A¨ìSheet2ÂI¿ï¡u²M°£¡vªº«ö¶s

¡ô¥i¥H¥Î¥¨¶°¬Ù²¤³o¨Ç¨BÆJ¶Ü?
«ü¥O¬O¸ò¤â°Êªº¨BÆJ¤@¼Ë¶Ü?

TOP

¦^´_ 20# GBKEE


    ÁÂÁÂGBKEEª©¥D¤£¹½¨ä·Ðªº¦^´_§Ú°Ýªº¤p°ÝÃD¡A§Ú­è­è¤w¸Õ¥X§Ú­nªºµª®×¤F¡A«D±`·PÁ±zªº«ü¾É~~
  1. Sub ²M°£_Click()
  2.     Sheet2.UsedRange.Offset(1, 0).Clear
  3.     Sheet1.UsedRange.Range("B4:B60300").Clear
  4.     Sheet1.UsedRange.Range("C1").Clear
  5.     Sheet1.UsedRange.Range("E1").Clear
  6.     Sheet1.UsedRange.AutoFilter Field:=2
  7.     Sheet1.UsedRange.AutoFilter Field:=6
  8. End Sub
½Æ»s¥N½X

TOP

¦^´_ 20# GBKEE


    GBKEEª©¤j±z¦n¡A·Q¦A¦V±z½Ð±Ð¤@¤U¡A¦pªG·Q±N¼Æ¶q¨Ì¨Ï¥ÎªÌ¿é¤Jªº¼Æ¶q¥h·s¼W¦Ü¡i¬d¸ß¡j¤u§@ªíBÄæ¸Ì¡A¨Ã±a¥X¨ä¥L¹ïÀ³©Î­pºâ¥X²Å¦XªºÄæ¦ì­È¡A
¸Ô¦pªþ¥ó¡A½Ð±Ð¸Ó¦p¦ó°µ©O?³W«h¦b¡i¬d¸ß¡j¤u§@ªí¤¤ªºÄæ¦ì¦WºÙ¦³¥[¤Jµù¸Ñ¡AÁÂÁ±z^^

¥Ø«eªþ¥ó¡i¬d¸ß¡j¤u§@ªí¤¤ªºÄæ¦ì¬O§Æ±æ¥i¥H³Ì²×Åã¥Üªºµ²ªG




VBAtest7.rar (14.21 KB)

TOP

¦^´_ 20# GBKEE


    GBKEEª©¥D¡A¤£¦n«ä·N¡A¦]¬°¹ê¦b§ä¤£¥X¿ù»~ªº¦a¤è¡A¥i¥H³Ò°Ê±z¤j¾rÀ°¦£¬Ý¤@¤U­þ¸Ì¦³°ÝÃD¶Ü?
    °õ¦æ´ú¸Õµ²ªG¦b¿é¤J²Ä¤@µ§¤Î²Ä¤Gµ§¸ê®Æ¬O¨S°ÝÃD¡A¦ý²Ä¤Tµ§¤§«á´N·|¿ù¶Ã¤F¡A°²³]²Ä¤@µ§¦b°Ó«~F¿é¤J¼Æ¶q«á¡A²Ä¤Gµ§ªº¦ì¸m¨S­­¨î¡A¦ý²Ä¤Tµ§´N¤@©w­n¦b°Ó«~F¤§«e(¨Ò°Ó«~E)¤~·|±a¤J   Sheet2¡A¦b°Ó«~F¤§«á(¨Ò°Ó«~N)´N±a¤£¹L¥hSheet2¡A¬°¤°»ò·|³o¼Ë??
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)  '***¥¦¬O¤u§@ªíªºÄ²°Ê¨Æ¥ó ***
  3.     Dim Target_Row As String, xi As Integer, xi_Row As String
  4.     If Not Application.Intersect(Range("A2", Range("A2").End(xlDown)), Target) Is Nothing Then
  5.         'Intersectª«¥ó
  6.         Target_Row = Target(, 1) & "," & Target(, 2)
  7. 'Join ¨ç¼Æ:¶Ç¦^¤@­Ó¦r¦ê¡A¸Ó¦r¦ê¬O³z¹L³sµ²¬Y­Ó°}¦C¤¤ªº¦h­Ó¤l¦r¦ê¦Ó«Ø¥ßªº¡C
  8.         xi = 5
  9.         Do While Sheets("sheet2").Cells(xi, 1) <> ""
  10.            xi_Row = Join(Application.Transpose(Application.Transpose(Sheets("sheet2").Cells(xi, 1).Resize(, 2))), ",")
  11.            If xi_Row = Target_Row Then Exit Sub
  12.            xi = xi + 1
  13.         Loop
  14.         Sheets("sheet2").Cells(xi, 1).Resize(, 2) = Split(Target_Row, ",")
  15.         'Split ¨ç¼Æ: ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C¡A¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê¡C
  16.     End If
  17. End Sub
½Æ»s¥N½X
test2.rar (8.96 KB)

TOP

¦^´_ 24# emma
  1. If Not Application.Intersect(Range("A2", Range("A2").End(xlDown)), Target) Is Nothing Then
  2. '§ï¦¨ B2 -[B2].End(xlDown) ¶¡¸ê®Æ¦C¦V¥ª¦ì²¾¤@Äæ (AÄæ)  
  3. If Not Application.Intersect(Range("B2", Range("B2").End(xlDown).Offset(, -1)), Target) Is Nothing Then
½Æ»s¥N½X
23#
¹Ï1»P¹Ï2¬Û¹ïÀ³ªº¸ê®Æ[§»¥þ]¬°¦ó·|¬O2µ§¸ê®Æ
²Ö¿nÂI¼Æ,¬¡°Êª¬ºA ¨âÄæ ¬O¦p¦ó§PÂ_!!

TOP

¦^´_ 25# GBKEE


    GBKEEª©¤j¡AÁÂÁ±z^^¡Atest2¤w¥¿±`~~~
    ¡u§»¥þ¡v·|¦³¤Gµ§¬O¦]¬°¡A§»¥þ¥i¥H¦³¦hµ§¤£¦Pªº¡u¼Æ¶q¡v¡A¨Ï¥ÎªÌ¨C¿é¤J¤@¦¸´N·|±a¤@µ§¨ì¡i¬d¸ß¡j¸Ì¡A³o¤]¬O§Ú¬ã¨stest2ªº­ì¦]
     ²Ö¿nÂI¼Æªº§PÂ_¬O²£«~¦³¤À¬°¡u¦³ÂI¼Æ²Ö¿n¡v¸ò¡u¨S¦³ÂI¼Æ²Ö¿n¡vªº©Ò¥H¤~¦b¸ê®ÆÀɤ¤«Ø¤@Äæ¦ì¬°¡iÂI¼Æ²Ö¿n¡j¡A¡iÂI¼Æ²Ö¿n¡jÄæ¦ì¬°¡uV¡v´N¬O¡u¦³ÂI¼Æ²Ö¿n¡v¡A¡iÂI¼Æ²Ö¿n¡jÄæ¦ì¬°¡uX¡v´N¬O¨S¦³¡u¨S¦³ÂI¼Æ²Ö¿n¡v¡A¦ý¤S¦s¦b²Ä¤G­Ó§PÂ_ªº±ø¥ó¡A´N¬O¡u¦³ÂI¼Æ²Ö¿n¡vªº¦³´Á¶¡­­¨î¡A¦Ó¥B¨C¤@¥ó²£«~ªº²Ö¿n´Á¶¡¤£¦P¡A¨ä§PÂ_±ø¥ó´N³]¦b¡uÂI¼Æ²Ö¿nµ²§ô¡v³o­ÓÄæ¦ì¡A¦p¥¼KEY¤Wµ²§ô¤é´Á«e(´N¬O«O¯dªÅ­È"")¡A¤~¯à­pºâ²Ö¿nÂI¼Æ¡A²Ä¤T­Ó±ø¥ó´N¬O²Ö¿nÂI¼Æ¬O¥H1000¬°³æ¦ì¡A¤p©ó1000´N¨S¦³²Ö¿n¡A¨Ò¦p1000~1999ºâ²Ö¿n1000¡B2000~2999ºâ²Ö¿n2000¡A¥H¦¹Ãþ±À¡C
¡@¡@¬¡°Êª¬ºA¬O·Q­n§PÂ_¹B¶O¬O§_­n­pºâ¡A¦b¡u¸ê®ÆÀÉ¡v¤u§@ªí¤¤¡A¡i¥D±À°Ó«~¡jÄæ¦ì¦³¡u±À¡v¦rªº¸Ü¡A´N¥i¥H§K¹B¡A¦ý«e´£¬O­n¼Æ¶q¦³¤j©ó¡i°ò¥»¶q¡jÄæ¦ì¡A¦pªG¨S¦³²Å¦X¤W­z±ø¥óªº¸Ü¡A´N­n¥[­p¹B¶O¡A·í¡i¥D±À°Ó«~¡jÄæ¦ì¨S¦³¡u±À¡v¦rªº¸Ü¡A¼Æ¶q¤S¤p©ó¡i°ò¥»¶q¡jÄæ¦ì®É¡A°£¹B¶O¥t­p¥~ÁÙ­n¦A¥[¯S§O³B²zªº¤âÄò¶O¡A¦ý¬O·í¤µ¤Ñ(·í¤Uªº¤é´Á¡A¦p101/11/15)ªº¤é´Á¶W¹L¡i³Ì«á¹wÁʤé¡j®É¡A´N¤£¥Î°µ³o¨Ç§PÂ_¡Aª½±µÅã¥Ü¡u¤wµ²§ô¡v¡A¦]¬°°Ó«~¤wµLªk¤U³æ¤F¡C

©Ò¥H§ÚÁÙº¡ÀY¤jªº¡A¤£¾å±o³o¼Ë¬O§_¯à¶Ç¹F²M·¡¡AÁÂÁÂGBKEEª©¤jªºÀ°¦£^^

TOP

¦^´_ 25# GBKEE

¹ï¤£°_¡A§ó¥¿¤@¤U Ãö©ó¡u¬¡°Êª¬ºA¡vªº»¡©ú
     1.¦pªG¼Æ¶q¤p©ó¡i°ò¥»¶q¡j´N¬O­n¦¬¨ú¡u¹B¶O¡v+¡u¤âÄò¶O¡v
     2.¦pªG¼Æ¶q¤j©ó¡i°ò¥»¶q¡j¦ý¬O¡i¥D±À°Ó«~¡jÄæ¦ì¨S¦³¡u±À¡v¦rªº¸Ü¡A¥u­n¦¬¨ú¡u¹B¶O¡v
     3.¦pªG¼Æ¶q¤j©ó¡i°ò¥»¶q¡j¦ý¬O¡i¥D±À°Ó«~¡jÄæ¦ì¥B¦³¡u±À¡v¦rªº¸Ü¡A´N¡u§K¹B¡v
     ps.·í¡i³Ì«á¹wÁʤé¡j(¨Ò101/11/15¤£§t·í¤Ñ)¡A¤]´N¬O¤µ¤Ñªº¡i³Ì«á¹wÁʤé´Á¡j¦pªG¬O101/11/14(§t)¤§«eªº¸Ü¡A´Nª½±µÅã¥Ü¡u¤wµ²§ô¡v


¸É¥R»¡©ú¡u²Ö¿nÂI¼Æ¡vªº§PÂ_
1.¡iÂI¼Æ²Ö¿n¡jÄæ¦ì¬°¡uV¡v¥B¡i³Ì«á¹wÁʤé¡j(¨Ò101/11/15§t·í¤Ñ)¡A´N­pºâ²Ö¿nÂI¼Æ¡A²Ö¿nÂI¼Æ¬O¥H1000¬°³æ¦ì¡A¤p©ó1000´N¨S¦³²Ö¿n¡A¨Ò¦p1000~1999ºâ²Ö¿n1000¡B2000~2999ºâ²Ö¿n2000¡A¥H¦¹Ãþ±À¡C
2.¦pªG¨S¦³§¹¥þ²Å¦X¤W­zªº±ø¥ó¡A¨Ò¦p¡iÂI¼Æ²Ö¿n¡jÄæ¦ì¬°¡uV¡v¥B¡i³Ì«á¹wÁʤé¡j¬°101/11/14(¤µ¤Ñ¥H«e)¡B¡iÂI¼Æ²Ö¿n¡jÄæ¦ì¬°¡uX¡v¡B¼Æ¶q¤p©ó1000¡A³o¨ÇªºÂI¼Æ´N¤£¥Î¥h­pºâ¤F¡C

¡@¡@ÁÂÁÂGBKEEª©¤jªºÀ°¦£^^

TOP

¦^´_ 27# emma
¬O¤£¬O³o¼Ë®ÄªG?
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim Ar(), A As Range
  3. If Target.Address <> "$B$1" Then Exit Sub
  4. If Application.CountA(Range([A5], Cells(Rows.Count, 1))) > 0 Then
  5.    For Each A In Range([A5], Cells(Rows.Count, 1).End(xlUp))
  6.       ReDim Preserve Ar(s)
  7.       Ar(s) = Application.Transpose(Application.Transpose(A.Resize(, 8)))
  8.       s = s + 1
  9.     Next
  10. End If
  11.    
  12. With Sheet1
  13. If Application.Count(.Range("B:B")) > 0 Then
  14.    For Each A In .Range("B:B").SpecialCells(xlCellTypeConstants, 1)
  15.    ReDim Preserve Ar(s)
  16.    If A.Offset(, 8) = "V" And (A.Offset(, 9) > Date Or A.Offset(, 9) = "") And A > A.Offset(, 4) Then dot = Int(A / 1000) * 1000 Else dot = 0
  17.    k = IIf(Target = "Á`©±", 10, 11)
  18.       If A < A.Offset(, 4) Then
  19.          m = "¹B¶O+¤âÄò¶O"
  20.          ElseIf A.Offset(, 5) = "±À" And A > A.Offset(, 4) Then
  21.          m = "§K¹B"
  22.          ElseIf A.Offset(, 5) <> "±À" And A > A.Offset(, 4) Then
  23.          m = "¹B¶O"
  24.       End If
  25.    Ar(s) = Array(A.Offset(, 2).Value, A.Value, A.Offset(, 3).Value, dot, A.Offset(, 12).Value, A.Offset(, k).Value, m, A.Offset(, 6).Value)
  26.    s = s + 1
  27.    Next
  28. End If
  29. End With
  30. If s > 0 Then [A5].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
  31. Range("A4").CurrentRegion.Sort key1:=[A4], Header:=xlYes
  32. End Sub
½Æ»s¥N½X
VBAtest7.rar (19.28 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 28# Hsieh

¬Oªº¡AÁÂÁÂHsiehª©¤j¡A¤j­P¤W¬O³o¼Ëªº®ÄªG¡A¦ý¬Oªì¨B¨Ï¥Î§Ú·Q¦pªG¦³¤G­Ó¦a¤è¥i¥H½Õ¾ã¤@¤Uªº¸Ü·|§ó¦n
1.¤u§@ªí¦³¡i¤À©±¡j»P¡iÁ`©±¡j¦@³q¬d¸ß¨Ï¥Î¡A¦ý¤j³¡¥÷¨Ï¥Îªº¤H­û¥u·|¥u¬d¦Û¤v©Ò¦bªºÂI¡A©Ò¥H¦pªG¬O¡iÁ`©±¡jÀ³¸Ó´N·|©T©w¬d¸ß¡iÁ`©±¡j
¦b¡i¸ê®ÆÀÉ¡j¿é¤J¼Æ¶q«á¥²»Ý¦Ü¡i¬d¸ß¡j¦AÂI¿ï¡u©±§O¡vªºÄæ¦ì¤~·|±a¥X¸ê®Æ¡A¥i¥H¥ý¨Ì¹w³]ªº¡u©±§O¡vª½±µ±a¥X¸ê®Æ¶Ü?
¦Ó¥B¥u­n¤Á´«¤@¦¸¡u©±§O¡v´N·|±N¡i¸ê®ÆÀÉ¡j©Ò¦³¿é¤J¼Æ¶qªºÄæ¦ì¦A±a¶i¡i¬d¸ß¡j¤@¦¸(¦p¹Ï¤@¦Ü¹Ï¤T)

2.¬¡°Êª¬ºA¤Ö¤F§PÂ_¡i³Ì«á¹wÁʤé¡j³o­Ó±ø¥ó¡A¦pªG¤µ¤Ñªº¤é´Á¤j©ó¡i³Ì«á¹wÁʤé¡j´NÅã¥Ü¡u¤wµ²§ô¡v´£¿ô¨Ï¥ÎªÌ¦¹µ§°Ó«~µLªk³QÁʶR¡C




¡õ¥H¦¹¹Ï¬°¨Ò¡A°£¤F¡iªYëù¡j¥H¥~¡A¨ä¥Lµ§ªº¬¡°Êª¬ªp¦pªG¥i¥H³]¬°¤wµ²§ôªº¸Ü´N§ó¦n¤F¡C


·íµM¡A§Ú¤]·|¦Û¤v§V¤O¸Õ¸Õ«ç»ò§¹¦¨¦Û¤v·Q­nªº®ÄªG¡AÁÂÁÂHsiehª©¤jªºÀ°¦£^^

TOP

¦^´_ 28# Hsieh

Hsiehª©¤j¡A¤£¦n·N«ä¡A®¤¤pªº·M²Â¡A§Ú¸Õ¤F¤@¾ã¤Ñ¤~¸Õ¥XÃö©ó¬¡°Êª¬ºAªº³¡¥÷¦Ó¥H¡A©Ò¥H¥u¦n¦A¨D±Ï¤@¤U¡A
¦¹¤èªk§âµ{¦¡¼g¦b¡i¬d¸ß¡jªº¡u©±§O¡vÄæ¦ì¸Ì¡A©Ò¥H¥u¦³¦bÅܰʨì¡u©±§O¡vªºÄæ¦ì®É¤~¯à§ó·s¨ì¡i¬d¸ß¡jªº¤º®e¡A
©Ò¥H¦pªG·QÅý¨Ï¥ÎªÌ¦b¡i¸ê®ÆÀÉ¡j¤@Åܧó¼Æ¶qÄæ¦ì®É´N¥i¥H§ó·s¦Ü¡i¬d¸ß¡j¤u§@ªíªº¸Ü¡AÀ³¸Ó­n¦p¦ó³]©w¤~¦n©O?
¥Ø«eªº¤u§@ªí¤£¬O¤£¯à¨Ï¥Î¡A¦ý¬O¨Ï¥Î¤W¦³¤G­Ó°ÝÃD
¤@¬O¥²»Ý±N¡i¸ê®ÆÀÉ¡j¸Ìªº¼Æ¶qÄæ¦ì¥ý²M°£±¼¡A­«·s¥ukey¤J·Q·s¼Wªº¼Æ¶q¡A¦A¦Ü¡i¬d¸ß¡j¤u§@ªí¿ï¾Ü©±§O¡A´N¥i¥H¶¶§Q·s¼W¡A
¦ý¦pªG¨S¦³¥þ³¡²M°£ªº¸Ü¡A¸ê®Æ·|²V¦b¤@°_¤À¤£²M¡uÀx¦ì¡v¬O­þ­Ó©±§Oªº¡uÀx¦ì¡v¡A§Æ±æ±z¯à©ú¥Õ§Ú¦b±Ô­z¤°»ò¡A´Ý©Àªº§Æ±æ¶g¥½¦^¨Ó«á¡A
¯à¦A¦³©Òºë¶i¡A¯¬±z¶g¥½´r§Ö^^
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim Ar(), A As Range
  3. If Target.Address <> "$B$1" Then Exit Sub
  4. If Application.CountA(Range([A5], Cells(Rows.Count, 1))) > 0 Then
  5.    For Each A In Range([A5], Cells(Rows.Count, 1).End(xlUp))
  6.       ReDim Preserve Ar(s)
  7.       Ar(s) = Application.Transpose(Application.Transpose(A.Resize(, 8)))
  8.       s = s + 1
  9.     Next
  10. End If
  11.    
  12. With Sheet1
  13. If Application.Count(.Range("B:B")) > 0 Then
  14.    For Each A In .Range("B:B").SpecialCells(xlCellTypeConstants, 1)
  15.    ReDim Preserve Ar(s)
  16.    If A.Offset(, 8) = "V" And (A.Offset(, 9) > Date Or A.Offset(, 9) = "") And A > A.Offset(, 4) Then dot = Int(A / 1000) * 1000 Else dot = 0
  17.    k = IIf(Target = "Á`©±", 10, 11)
  18.       If A.Offset(, 7) >= Date Then
  19.       If A < A.Offset(, 4) Then
  20.          m = "¹B¶O+¤âÄò¶O"
  21.          ElseIf A.Offset(, 5) = "±À" And A > A.Offset(, 4) Then
  22.          m = "§K¹B"
  23.          ElseIf A.Offset(, 5) <> "±À" And A > A.Offset(, 4) Then
  24.          m = "¹B¶O"
  25.       End If
  26.    Else
  27.    m = "¤wµ²§ô"
  28.    End If
  29.    Ar(s) = Array(A.Offset(, 2).Value, A.Value, A.Offset(, 3).Value, dot, A.Offset(, 12).Value, A.Offset(, k).Value, m, A.Offset(, 6).Value)
  30.    s = s + 1
  31. Next
  32. End If
  33. End With
  34. If s > 0 Then [A5].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
  35. Range("A4").CurrentRegion.Sort key1:=[A4], Header:=xlYes
  36. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD