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

[µo°Ý] ¬d¸ß²£«~¼Æ¶q

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 31# GBKEE


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

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

¶i¥X³fªí³æ¤ÀÃþ0913.rar (12.19 KB)

owen

TOP

¦^´_ 32# owen9399
¿ï¾Ü¶i³fªí¤¤ªº¸ê®Æ½d³ò,¥Î«ü¥O[¸ê®Æ]-> [¦Û°Ê¿z¿ï],±N¦U·~°È­û¨Ì¦¸ªº¿z¿ï¸ê®Æ,½Æ»s¨ì¦U·~°È­ûªº¤u§@ªí¸Ì
¦Û¤v¸Õ¸Õ,¥Î¿ý»s¥¨¶°,¬Ý¬Ýµ{¦¡½X,½m²ß¤@¤U,
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

¶i¥X³fªí³æ¤ÀÃþ0917.rar (19.93 KB)

owen

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ owen9399 ©ó 2013-10-8 11:15 ½s¿è

¦^´_ 35# GBKEE


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

  ÁÂÁÂ
owen

TOP

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

«ü¥O:
Sheet2.Activate
owen

TOP

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ªþ¥ó

¶i¥X³fªí³æ¤ÀÃþ1008.rar (23.31 KB)

owen

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-10-11 14:49 ½s¿è

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2013/10/11 14:51): 39# ªºµ{¦¡¦³»~,¤w§ó¥¿,

¥»©«³Ì«á¥Ñ owen9399 ©ó 2013-10-11 14:43 ½s¿è

¦^´_ 39# GBKEE


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

¶i¥X³fªí³æ¤ÀÃþ1011-1.rar (23.64 KB)

owen

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD