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

[µo°Ý] ¦p¦ó¨Ì¥X³f³æªº¥X³f³æ¸¹¡A¥s¥X¤§«e¦s¦b¦b²M³æ¤¤ªº¸ê®Æ??

[µo°Ý] ¦p¦ó¨Ì¥X³f³æªº¥X³f³æ¸¹¡A¥s¥X¤§«e¦s¦b¦b²M³æ¤¤ªº¸ê®Æ??

¥»©«³Ì«á¥Ñ §±æ¢ ©ó 2014-3-28 14:57 ½s¿è

§Úªº¥X³f³æ¦³³]©w¹L±bªº¥¨¶°¡A«ö¹L±b«ö¶s¡A¸ê®Æ´N·|¦s¦b²M³æªº¤u§@ªí¤¤
§Ú·Q§YµM¦³¿ìªk¨Ï¥Î¥¨¶°±N¥X³f³æªº¸ê®ÆÂà¦s¦b²M³æ¤¤
¬O¤£¬O¤]¦³¿ìªk§@¤@­Ó¥¨¶°¡AµM«á¨Ì¡u¥X³f³æ¸¹¡v±N¦s¦b²M³æ¤¤ªº¬ÛÃö¸ê®Æ³£¥s¦^¨Ó?

½Ð«e½ú«ü±Ð!!:)

ª«®ÆºÞ²z3¤ë28¤é.zip (299.04 KB)

¦^´_ 1# §±æ¢
¥X³f³æ¼Ò²Õ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim Ar(), s%
  3. Application.EnableEvents = False
  4. If Target.Address = "$H$5" Then
  5. With Sheets("¥X³f²M³æ")
  6.    For Each a In .Range(.[L3], .[L3].End(xlDown))
  7.      If a = Target Then
  8.      m = a.Offset(, -2).Value
  9.         ReDim Preserve Ar(s)
  10.         Ar(s) = Application.Transpose(Application.Transpose(a.Offset(, -11).Resize(, 8).Value))
  11.         s = s + 1
  12.      End If
  13.    Next
  14. End With
  15. Range([A9].Resize(, 8), [A9].Resize(, 8).End(xlDown)).ClearContents
  16. [C3] = m
  17. [A9].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
  18. End If
  19. Application.EnableEvents = True
  20. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# §±æ¢
¸Õ¸Õ¬Ý:
  1. '
  2. Private Sub ¥s¦^¹L±b_Click()
  3.     Dim i, blankRow As Integer
  4.     Dim ¥X³f³æ¸¹1, ¥X³f³æ¸¹2 As String
  5.     Dim sh1, sh2 As Worksheet
  6.     Set sh1 = Sheets("¥X³f³æ")
  7.     Set sh2 = Sheets("¥X³f²M³æ")
  8.     ¥X³f³æ¸¹1 = sh1.[H5]
  9.    
  10.     blankRow = sh1.[A65536].End(xlUp).Row + 1
  11.     For i = 3 To 65536
  12.         ¥X³f³æ¸¹2 = sh2.Cells(i, 12)
  13.         
  14.         If ¥X³f³æ¸¹2 = "" Then
  15.             Exit Sub
  16.         ElseIf ¥X³f³æ¸¹2 = ¥X³f³æ¸¹1 Then
  17.             sh2.Cells(i, 1).Resize(1, 14).Copy sh1.Cells(blankRow, 1)
  18.             blankRow = blankRow + 1
  19.         End If
  20.     Next
  21. End Sub
½Æ»s¥N½X

ª«®ÆºÞ²z3¤ë28¤é.7z
http://www.mediafire.com/download/55eqy81iu923mf3/%E7%89%A9%E6%96%99%E7%AE%A1%E7%90%863%E6%9C%8828%E6%97%A5.7z

TOP

ÁÂÁ¤G¦ì«e½ú!!
¤§«e³£¥Î¥¨¶°¡A©Ò¥Hªá¤F«Ü¤[ªº®É¶¡¦b¬Ý¡A¤£¹LÁÙ¬O¦³«Ü¦h¤£À´ªº
µ{¦¡¹B¦æ³£ok¡A·P®¦©O!!

TOP

¦^´_ 4# yen956

½Ð°Ý¡APrivate Sub  ¥s¦^¹L±b_ClickªºPrivate ¬O¨p¦³µ{§Ç¡A·í§Ú«ö«ü©w¥¨¶°®É¡A¨S¬Ý¨ì¡A­n±NPrivate§R±¼¤~¥i¥H¡A­n«ç»ò°µ¤~¥i¥H¦b¥¨¶°¤¤«ü©w?

µ{¦¡¹B¦æ¦³­Ó°ÝÃD¡A¥s¦^¹L±b«á²M°£¸ê®Æ®É¡A(GÄæ)¡B(JÄæ)¡B(KÄæ)¡B(LÄæ)¡B(MÄæ)¡B(NÄæ)¨S¿ìªk²M°£¡A»Ý¤â°Ê²M°£¡A¦p¦¹¤@¨Ó¼g¤Jªº¨ç¼Æ¤]¸òµÛ³Q§R±¼¤F
GÄæ¬O¹ïÀ³EÄæ¡AJÄæ¡BKÄæ¡BLÄæ¡BMÄæ¡BNÄæ¬Oª½±µ=¬Y¨ÇÄæ¦ì¡A³o´X­Ó¬OÁôÂð_¨Óªº¡Aµ{¦¡¸Ó¦p­×§ï?¥t¥~¤u§@ªí¦³³]«OÅ@¡A§Ú±NSheets("¥X³f³æ").Unprotect "0523" ¼g¦bµ{¦¡¶}©l«á¡A±N  Sheets("¥X³f³æ").protect "0523"¼g¦bµ{¦¡µ²§ô«e¡A¦ý±`±`·|¥X²{¿ù»~ªºµøµ¡¡A¤£µM´N¬O«ö§¹¥¨¶°«á¡A«OÅ@¤u§@ªí¨ú®ø¤F¡C¥Ø«e§Úªº¤èªk¬O·s¶}¤@±i¤u§@ªí¡C

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-4-13 14:11 ½s¿è

¦^´_ 6# §±æ¢
©êºp, ¨S¦Ò¼{¨ì¤½¦¡¦^ÂЪº°ÝÃD,
¤£¹L§Ú¹ï¤½¦¡ªº¤F¸ÑÁÙ¬O«Ü½§²L, ·R²ö¯à§U.
§A¥i«ô°UHª©¤j¸Õ¸Õ
¤S, ·sªº Command «ö¶s(2000¥H«á)µLªk«ü©w¥¨¶°,
§A¥i½Æ»sªº«ö¶s, ­«·s©R¦W, ´N¥i«ü©w¥¨¶°,

TOP

¦^´_ 7# yen956


   ¦nªº¡AÁÂÁ§A³á!!

TOP

¦^´_ 2# Hsieh


    ½Ð°Ý§Ú±Nµ{¦¡¶K¤W«á¡AÂI«ü©w¥¨¶°§ä¤£¨ì Worksheet_Change(ByVal Target As Range)
    ­n«ç»ò¨Ï¥Î©O?

TOP

¦^´_ 9# §±æ¢
2# Hsieh ¦³»¡
  1. ¦^´_ 1# §±æ¢
  2. ¥X³f³æ¼Ò²Õ
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 10# GBKEE


    ·PÁ§A ¡A¬ã¨s¤F«Ü¤[¡A¤F¸Ñ«ç»ò¨Ï¥Î¤F¡A±q¤¤¾Ç¨ì¤F¨Ç¡Aµo²{§Úªº°ÝÃD¤]§ó¦h:D

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD