GBKEE

¦^´_ 34# emma

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     Dim Target_Row As String, s As Integer, dot As Long, k As Integer, m As String
   
4.     Dim Ar(), A As Range
   
5.     If Target.Address(0, 0) = "E1" Then
   
6.         Range("D3").AutoFilter Field:=2, Criteria1:="*" & Target & "*"
   
7.     ElseIf Target.Address(0, 0) = "C1" Then
   
8.         Range("C3").AutoFilter Field:=1, Criteria1:="*" & Target & "*"
   
9.     End If
   
10.     With Sheet1
    
11.         If Application.Count(.Range("B:B")) > 0 Then
    
12.             For Each A In .Range("B:B").SpecialCells(xlCellTypeConstants, 1)
    
13.                 ReDim Preserve Ar(s)
    
14.                 If A.Offset(, 8) = "V" And A.Offset(, 9) >= Date And A > A.Offset(, 4) Then dot = Int(A / 1000) * 1000 Else dot = 0
    
15.                 k = IIf(Sheets("¬d¸ß").[B1] = "Á`©±", 10, 11)
    
16.                 If A.Offset(, 7) < Date Then
    
17.                     m = "¤wµ²§ô"
    
18.                 ElseIf A < A.Offset(, 4) Then
    
19.                     m = "¹B¶O+¤âÄò¶O"
    
20.                 ElseIf InStr(A.Offset(, 5), "±À") And A > A.Offset(, 4) Then       '¥]§t
    
21.                     m = "§K¹B"
    
22.                 ElseIf InStr(A.Offset(, 5), "±À") = 0 And A > A.Offset(, 4) Then   '¤£¥]§t
    
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.     With Sheets("¬d¸ß")
    
31.         If s > 0 Then
    
32.             Target = ""
    
33.             .Range("A" & .Rows.Count).End(xlUp).Offset(1).Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
    
34.             .Range("A4").CurrentRegion.Sort key1:=.[A4], Header:=xlYes
    
35.         End If
    
36.     End With
    
37. End Sub

½Æ»s¥N½X

emma

¦^´_ 31# Hsieh


    Hsiehª©¤j¡AÁÙ¦³¤@­Ó¦a¤è·Q¶¶«K½Ð±Ð¤@¤U¡A´N¬O¡õ³o­Ó¦a¤è¥i¤£¥i¥H§ï¦¨¡u¥]§t¡v»P¡u¤£¥]§t¡vªº¥Îªk?¦ý§Ú¤£ª¾¹D¤£¥]§tªº»yªk¬O¤°»ò¡A©Ò¥H³o»ò¸Õªºµ²ªG·íµM¬O¸ò§Ú»¡»yªk¿ù»~°Õ ¡AÁÂÁ±z!!

1. ElseIf A.Offset(, 5) = "±À" And A > A.Offset(, 4) Then
   
2.          m = "§K¹B"
   
3.          ElseIf A.Offset(, 5) <> "±À" And A > A.Offset(, 4) Then

½Æ»s¥N½X

1. ElseIf A.Offset(, 5) Like "*±À*" And A > A.Offset(, 4) Then
   
2.          m = "§K¹B"
   
3.          ElseIf A.Offset(, 5) Not Like "*±À*" And A > A.Offset(, 4) Then

½Æ»s¥N½X

cea

ÁÂÁ¤À¨É
·Pı¦n¦h­n¾Çªº³á
¤j¤j­Ì³£¤Ó±j¤F

emma

¦^´_ 31# Hsieh


    «D±`·PÁÂHsiehª©¤j¡BGBKEEª©¤j¡A±o¤G¦ì°ª¤âªº¨ó§U¡A§Ú¤wºî¦X¥X§Ú·Q­nªºª©¥»¤F¡A
    Hsiehª©¤j¡A¨ä¹ê§Ú¤]¥u¬O·Ó¨Ï¥ÎªÌ·Q­nªº¥\¯à¥h¬ã¨s¡A¬O§Æ±æ¨Ï¥ÎªÌ¦b¿é¤J¼Æ¶q®É¡A¯à²¤Æ¤H­û¥Î¤H¤u§PÂ_ªº±ø¥ó¡A¥H´î¤Ö¤H¤u§PÂ_ªº¿ù»~¡A©Ò¥H´N...¦¨¤F³o¼Ëªº¤u§@ªí¤F¡C

PS.§Ú·Q­n¦A½Ð±Ð¤@­Ó°ÝÃD¡A¯à¤£¯àÅý¨Ï¥ÎªÌ¦b¿é¤J§¹¼Æ¶q¡A±N¸Óµ§¼Æ¶q¼g¦Ü¡i¬d¸ß¡j¤u§@ªí¡A´N¥ß§Y²MªÅ¸ÓÀx¦s®æ?¨Ò¦p¦bB3¿é¤J§¹1234Â÷¶}«á¡A±NB3¦C¸ê®Æ±a¤J¡i¬d¸ß¡j¤u§@ªí¸Ì¡A¦A¥ß§Y±NB3ªºÀx¦s®æ²MªÅ!!¦³³oºØ¤èªk¶Ü?

ªþ¤W³Ì«á§¹¦¨ªº½m²ß½d¥»¡A©Î³\ª©¤j­Ì¥i¥H§ó²z¸Ñ§Ú°ÝPS.ªº¤èªk¥Î·N
VBAtest9.rar (36.62 KB)

¤U¸ü¦¸¼Æ: 8

2012-11-20 13:43

Hsieh

¦^´_ 30# emma

§Úı±o©pªº»Ý¨D¤£¹³¬O¬d¸ß¸ê®Æ
¤ñ¸û¹³¬O§â¸ê®ÆÀɤu§@ªíBÄ榳¼Æ値ªº¦C
¸g¹L³B²z«á¼g¤J¬d¸ß¤u§@ªí
¸Õ¸Õªþ¥ó
VBAtest7.rar (17.2 KB)

¤U¸ü¦¸¼Æ: 10

2012-11-17 21:43

emma

¦^´_ 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

emma

¦^´_ 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

¤U¸ü (198.39 KB)

2012-11-16 12:52

¤U¸ü (96.47 KB)

2012-11-16 12:52

¤U¸ü (110.53 KB)

2012-11-16 12:52

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

¤U¸ü (128.93 KB)

2012-11-16 12:52

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

Hsieh

¦^´_ 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)

¤U¸ü¦¸¼Æ: 12

2012-11-15 22:46

emma

¦^´_ 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ªºÀ°¦£^^

emma

¦^´_ 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ªºÀ°¦£^^