GBKEE

¦^´_ 30# owen9399
½Ð¦b¹ï·Ó 29# ¬Ý¬Ý§Ú»¡¤F¤°»ò!

1. Private Sub CommandButton3_Click() 'UserForm2ªº¬d¸ßµ{¦¡½X
   
2. Dim I As Integer, Rng As Range
   
3.      With Sh(2)
   
4.           .Range("AA1").CurrentRegion = ""  '¥[³o¦æ UserForm2ªí³æ°õ¦æ¤@¦¸«á¥i¤£»Ý³oµ{¦¡½X
   
5.          .AutoFilterMode = False
   
6.          For I = 1 To UBound(ar)
   
7.             If ar(I) <> "" Then .Range("A1").AutoFilter I, ar(I)
   
8.          Next
   
9.         .Range("A:h").SpecialCells(xlCellTypeVisible).Copy .Range("AA1")
   
10. '­ì¥»¬O .Range("A:i").SpecialCells(xlCellTypeVisible).Copy .Range("AA1")
    
11.          .AutoFilterMode = False
    
12.          Set Rng = .Range("AA1").CurrentRegion.Offset(1)
    
13.      End With
    
14.   ListBox1.RowSource = Rng.Address
    
15. End Sub

½Æ»s¥N½X

owen9399

¦^´_ 31# GBKEE


    Dear ¤j¤j:
  §Ú¦³·sªº°ÝÃD ­n°Ý
  ¦pªþ¥ó
  
  ±N ¿é¤J¶i³fªíªº¼Æ¾Ú ¤À¥Xµ¹ ªü¬ü ªü¤¯ ¤p¤ý

¨Ã§â¬Û¦P¤½¥q ªº¼Æ¶q Á`­p  (¶i³f ¥X³f Á`­p)  
  ÁÂÁÂ

GBKEE

¦^´_ 32# owen9399
¿ï¾Ü¶i³fªí¤¤ªº¸ê®Æ½d³ò,¥Î«ü¥O[¸ê®Æ]-> [¦Û°Ê¿z¿ï],±N¦U·~°È­û¨Ì¦¸ªº¿z¿ï¸ê®Æ,½Æ»s¨ì¦U·~°È­ûªº¤u§@ªí¸Ì
¦Û¤v¸Õ¸Õ,¥Î¿ý»s¥¨¶°,¬Ý¬Ýµ{¦¡½X,½m²ß¤@¤U,

owen9399

¦^´_ 33# GBKEE


    Dear ¤j¤j :

   §Ú¦³°µ¥X ³¡¥÷ªº¿z¿ï «ö ªü¬üÁ`®w¦s «ö¶s ,¦AÂI¿ï  ªü¬üÁ`®w¦s ªºªí ´N¤ÀÃþ¥X¨Ó

   1.¿z¿ï«áªº¥[Á` ¤£·|
   2.«ö ªü¬üÁ`®w¦s «ö¶s ¦Û°Ê¶]¨ì ¨q¥X ªü¬üÁ`®w¦sªº¸ê®Æªí


  ÁÂÁÂ

¯¬ ¤¤¬î¨Î¸` ´r§Ö

GBKEE

¦^´_ 34# owen9399
¶i¨B¦n¦h,Æg.

1. Private Sub CommandButton3_Click()
   
2.     Dim y
   
3.     Dim Joken3 As String
   
4.     Joken3 = "¤p¤ý"
   
5.     If Joken3 = "" Then Exit Sub
   
6.     With Sheets("¶i³fªí")
   
7.         '­Y¦b¿z¿ï¤¤¡A¥ý¸Ñ°£Åã¥Ü¥þ³¡
   
8.         If .FilterMode Then .ShowAllData
   
9.         '¥H¡e²Ä5Äæ¡D·~°È­û¡f¶i¦æ¿z¿ï
   
10.         .[A1].AutoFilter Field:=5, Criteria1:=Joken3
    
11.         '¨ú±o¿z¿ï«á¡A³Ì«á¤@µ§¸ê®Æªº¡e¦C¸¹¡f
    
12.         y = .[A65536].End(xlUp).Row
    
13.         '¶i¦æ¶KÂà¸ê®Æ
    
14.         .Range("A1:F" & y).Copy [¤p¤ýÁ`®w¦s!A1]
    
15.         '¦A«ì´_¥þªí
    
16.         .ShowAllData
    
17.         '¨ú®ø[¦Û°Ê¿z¿ï] ¤U©Ô½b¸¹
    
18.         .[A1].AutoFilter
    
19.         MsgBox [SUM(¤p¤ýÁ`®w¦s!F:F)]  '¿z¿ï«áªº¥[Á`
    
20.   End With
    
21.   Beep
    
22. End Sub

½Æ»s¥N½X

owen9399

¦^´_ 35# GBKEE


    dear ¤j¤j:
   ½Ð°Ý¤@¤U ¦p¦ó¦b ¶i³fªí ¤¤ , «ö¤@¤U ªü¬ü®w¦sªí ´N¨q¥X¨Ó

  ÁÂÁÂ

owen9399

¤j¤j
§Ú§ä¥X¨Ó¤F
¦p¦ó¦b ¶i³fªí ¤¤ , «ö¤@¤U ªü¬ü®w¦sªí ´N¨q¥X¨Ó

«ü¥O:
Sheet2.Activate

owen9399

DEAR GBKEE ª©¤j:
§Ú¦b ªü¬ü®w¦sªí¤¤ ¥[¤J ¬d¸ß¥\¯à
¬°¨ç¼Æ§ï¬° VBAµ{¦¡

¦p¦ó §ï¦¨ VBA µ{¦¡
±N ¤U©Ô¦¡¿ï³æªº ¬d¸ß¤½¥q ­«ÂЪº ¤£Åã¥Ü¥X¨Ó , ¨Ã²Î­p ¥X³f¼Æ¶q »P ¶i³f¼Æ¶q ªºÁ`¦X

ÁÂÁÂ

¦pªþ¥ó

GBKEE

¦^´_ 38# owen9399
[ªü¬üÁ`®w¦s] ¼Ò²Õ¹w³]¨Æ¥óµ{§Ç¤§µ{¦¡½X

1. Option Explicit
   
2. Private Sub Worksheet_Activate()          '¬¡­¶Ã¯: ¿ï¾Ü¤u§@ªíªº¹w³]¨Æ¥ó
   
3.     '¶i¶¥¿z¿ï  ¤½¥q¦WºÙ  'Columns.Count ->¤u§@ªíªºÁ`øó¼Æ ->³Ì«á¤@øó
   
4.     Range("B:B").AdvancedFilter xlFilterCopy, , Cells(1, Columns.Count), True
   
5.     With Range("I2").Validation   '¸ê®ÆªºÅçÃÒ²M³æ
   
6.         .Delete
   
7.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
   
8.         xlBetween, Formula1:="=" & Range(Cells(2, Columns.Count).Address, Cells(1, Columns.Count).End(xlDown).Address).Address
   
9.         .IgnoreBlank = True
   
10.       
    
11.     End With
    
12. End Sub
    
13. Private Sub Worksheet_Change(ByVal Target As Range)
    
14.     Application.EnableEvents = False
    
15.     If Target.Address(0, 0) = "I2" Then
    
16.         Range("J2") = [SUMIF(B:B,I2,D:D)]   '¤u§@ªíªº¨ç¼Æ¦b VBA¥Î¤¤¬A¸¹­pºâ  [ <- ¤u§@ªíªº¨ç¼Æ  -> ]
    
17.         Range("K2") = [SUMIF(B:B,I2,F:F)]
    
18.     End If
    
19.     Application.EnableEvents = True
    
20. End Sub

½Æ»s¥N½X

owen9399

¦^´_ 39# GBKEE


    dear ª©¤j:
   §Ú¦³¶K¤W´ú¸Õ,¦ý¬O ¥X³f¼Æ¶q »P ¶i³f¼Æ¶q ¨ÃµL²Î­p  ,¥¼¦P¨B
   ¸Ó¦p¦ó°µ
   ÁÂÁÂ