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

(¤w¸Ñ¨M)¦p¦ó¥Î·j´M§ä¸ê®Æ«á½Æ»s¸Ó¦ì¸m¥H¤Uªº¸ê®Æ?

(¤w¸Ñ¨M)¦p¦ó¥Î·j´M§ä¸ê®Æ«á½Æ»s¸Ó¦ì¸m¥H¤Uªº¸ê®Æ?

¥»©«³Ì«á¥Ñ freeffly ©ó 2012-3-6 13:43 ½s¿è

§Ú·Q­n¥Î·j´Mªº¤è¦¡
¦pªþÀÉ
¦pªG·j´M¨ì²Ä¤@¦U¤u§@ªí[C1]ªº¸ê®Æ
´N½Æ»s¦U­Ó¤u§@ªí²Ä4¦C¤§«áªº¸ê®Æ

¦]¬°¨C¦U¤u§@ªí±Æªº¦ì¸m¦³ÂI¤£¬Û¦P
©Ò¥H¥Ø«e§Ú·Q¨ìªº¤è¦¡¬O¥Î·j´M
¦ý¬O¥Îvba§Ú¤£ª¾¹D¸Ó«ç»ò¥h§ì


ssdsa.rar (18.08 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

¦^´_ 1# freeffly


³o­Ó¤è¦¡°w¹ï¼Ó¥D§A­n·J¾ãªºsheet¤¤C1ªºÀx¦s®æ¸ê®Æ
¥h¿z¿ï§A¨C¤@­Ó¤ë¥÷ªº²Å¦X§AC1¸ê®ÆªºÄæ¦ì,±N¸Ó¸ê®Æ±a¥X¨Ó
´£¨Ñµ¹§A°Ñ¦Ò¸Õ¸Õ¬Ý~
¥i¥H±NC1ªºÀx¦s®æ³]©wÅçÃÒªº¤è¦¡¨Ó¬D¿ï¸ê®Æ~ ·|§ó¦X¥Î~
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim E As Worksheet

  3. If Target.Column = 3 And Target.Row = 1 Then

  4. N = 2
  5. A = Sheet1.Range("C1")
  6. Sheet1.Range("A2:D65536").ClearContents
  7. For Each E In Sheets
  8.     If E.Index <> 1 Then
  9.         Set F = E.Range("A1:IV1").Find(A, LookIn:=xlValues)
  10.         If Not F Is Nothing Then
  11.            K = E.Range("A1:IV1").Find(A).Column
  12.            I = 4
  13.            Do Until E.Cells(I, 1) = ""
  14.                With E
  15.                Sheet1.Cells(N, 1) = E.Cells(I, 1)
  16.                Sheet1.Cells(N, 2) = E.Cells(I, 2)
  17.                Sheet1.Cells(N, 3) = E.Cells(I, K)
  18.                Sheet1.Cells(N, 4) = E.Cells(I, K).Offset(0, 1)
  19.                N = N + 1
  20.                End With
  21.                I = I + 1
  22.             Loop

  23.         End If
  24.     End If
  25. Next
  26. End If
  27. End Sub
½Æ»s¥N½X
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 2# hugh0620


   ¤Ó¼F®`¤F
  ´X¦U°ÝÃD°Ý¨Ì¤U
1.
  If Target.Column = 3 And Target.Row = 1 Then
³o¤@¥y¥i¥H§ï¦¨ Target.Address = "$C$1" ¶Ü

2.²Ä8¦æ¬°¤°»ò­n³sD¤]³B²z?
3.
E.Index  ³o­Óªº·N«ä¬O?
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¥»©«³Ì«á¥Ñ hugh0620 ©ó 2012-2-22 18:03 ½s¿è
¦^´_  hugh0620

  ´X¦U°ÝÃD°Ý¨Ì¤U
1.
  If Target.Column = 3 And Target.Row = 1 T ...
freeffly µoªí©ó 2012-2-22 17:36


§Ú¶È¬O¨Ì§Ú©Òª¾¹Dªº¤è¦¡¨Ó¼¶¼g~ «Ü¦h§ó²`§ó²¤Æªº~ §Ú¤]ÁÙ¦b¾Ç²ß¤¤~

²Ä¤@­Ó°ÝÃD~
If Target.Column = 3 And Target.Row = 1 Then  exit sub
³o¥y¬OÅý§A³]©w¦bC1³o­Ó¦ì¸m¤~¥hIJµoµ{¦¡~
¦pªG¨C¤@­ÓÀx¦s®æ³£¬O¿z¿ïªº¦ì¸m~ ³o¼Ë¼gªk´N¦³©Ò¤£¦P

Target.Address = "$C$1"
³o­Ó§Ú¦³´ú¸Õ¹L~ ¤]¥i¥H³o¼Ë¼g~

²Ä¤G­Ó°ÝÃD
2.²Ä8¦æ¬°¤°»ò­n³sD¤]³B²z?
    ¦]¬°¦³¬Ý¨ì¼Ó¥Dªº¥t¥~¨â­ÓDATA SHEET «e­±¦UºØ¹ô­È¬O¨â­ÓÄæ¦ì¸ê®Æ~
    ¥[¤W¤é´Á»P¬P´Á ¦@¦³¥|¦CÄæ¦ì~
   
²Ä¤T­Ó°ÝÃD
     E.Index  ªº·N«ä: E¬O¬¡­¶¸¹½X~ ¦]§A­n¦s©ñ¸ê®ÆªºSHEET.index= 1
     ¦Óµ{¦¡¦b°õ¦æ¬O¨C¤@­ÓSHEET³£·|³Q¯Ç¤J¿z¿ï~
     ©Ò¥H~ ¥Î¤@­ÓIF¨Ó§PÂ_ ÁקK¦s©ñ¸ê®ÆªºSHEET¤]³Q­pºâ~ ¦³¥i¯à·|³y¦¨¿ù»~~
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 1# freeffly
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Address = "$C$1" Then
  3. Application.EnableEvents = False
  4. For Each a In Range("A2", [A2].End(xlDown))
  5.   sh = Format(a, "emm")
  6.   With Sheets(sh)
  7.      Set c = .Rows(1).Find([C1], lookat:=xlWhole)
  8.      r = Application.Match(a, .[A:A], 0)
  9.      If IsNumeric(r) And Not c Is Nothing Then a.Offset(, 2) = .Cells(r, c.Column) Else a.Offset(, 2) = ""
  10.   End With
  11. Next
  12. End If
  13. Application.EnableEvents = True
  14. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# hugh0620


    ®¦ ÁÂÁ¦^ÂÐ
   §Ú¤]ÁÙ¦³«Ü¦h­n¾Ç
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 5# Hsieh


    ·PÁª©¥D
   ³o­Ó¤è¦¡§ó²¼ä
   ³t«×·Pı§ó§Ö¨Ç
   ¤£¹L¦³¤@¨Ç¤£À´
   ®ø¤Æ«á¦A´£°Ý
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 5# Hsieh


    ¥J²Ó¬Ý¹L«áªO¥Dªº¤è¦¡»P¥t¥~¤@¦ì¤j¤j¤è¦¡¤£¦P(³B²z¤è¦¡)
    ¦pªG»¡·Q­n¦V¥t¤@¦ì¤j¤j¨º¼Ë§ì¨âÄæ­n«ç»ò§ï

   Application.EnableEvents = False  ¦b³o¸Ìªº¥Î³B¬O? ­nÅý¤°»ò¨Æ¥ó¤£µo¥Í¶Ü?
   sh = Format(a, "emm")  emm¦³¯S®í³W«h¶Ü ¬Ý¨ìª©¥D¨Ï¥Î¤~ª¾¹D¦³³oºØ¤è¦¡
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 4# hugh0620

               ­è¸Õ¤F¤@¤U ³o¸ÌÁÙ¥i¥H§ï³o¼Ë

                Sheet1.Cells(N, 1).Resize(, 4) = Array( _
                E.Cells(i, 1), _
                E.Cells(i, 2), _
                E.Cells(i, K), _
                E.Cells(i, K).Offset(0, 1))
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 7# freeffly


    H¤j¤j¬O¤@­Ó°ª¤â~ §Ú¤£À´ªº°ÝÃD~ ©ß¥X¨Ó~ ¦Uª©¤j©Î¤j¤j­Ì³£¯à«ü¾É¸Ñ´b­ò~

    H¤j¤jªºµ{¦¡  ­n¦³¤@©wªº±ø¥ó¤~¯à°õ¦æ~
    °õ¦æ®É­nª`·NSHEET1ªº¤é´Á¸ê®Æ~
    ­Y§Aªº¤é´Á¸ê®Æ¤£¥]§t¦bDATA¤¤ªº¤é´Á~ ´N·|²£¥Í¿ù»~
    Á|¨Ò~ §A´£¨Ñªº¸ê®Æ¬O9~10¤ë~  ­Y§ASHEET1ªº¤é´Á¦b94/9/1¥H«e©Î94/10/31¥H«áªº¤é´Á~
    ´N·|²£¥Í¿ù»~~
     ©Ò¥H~ ­n³]¤@­Ó¨¾¿ùªºµ{¦¡½X~ ¤£µM§A°õ¦æªº®É­Ô¨Sª`·N´N¸Ü±¾¦b¨ºÃä­ò~
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD