freeffly

¦^´_ 4# hugh0620


    ®¦ ÁÂÁ¦^ÂÐ
   §Ú¤]ÁÙ¦³«Ü¦h­n¾Ç

Hsieh

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

hugh0620

¦^´_  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¦¨¿ù»~~

freeffly

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

hugh0620

¦^´_ 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. 
   
4. If Target.Column = 3 And Target.Row = 1 Then
   
5. 
   
6. N = 2
   
7. A = Sheet1.Range("C1")
   
8. Sheet1.Range("A2:D65536").ClearContents
   
9. For Each E In Sheets
   
10.     If E.Index <> 1 Then
    
11.         Set F = E.Range("A1:IV1").Find(A, LookIn:=xlValues)
    
12.         If Not F Is Nothing Then
    
13.            K = E.Range("A1:IV1").Find(A).Column
    
14.            I = 4
    
15.            Do Until E.Cells(I, 1) = ""
    
16.                With E
    
17.                Sheet1.Cells(N, 1) = E.Cells(I, 1)
    
18.                Sheet1.Cells(N, 2) = E.Cells(I, 2)
    
19.                Sheet1.Cells(N, 3) = E.Cells(I, K)
    
20.                Sheet1.Cells(N, 4) = E.Cells(I, K).Offset(0, 1)
    
21.                N = N + 1
    
22.                End With
    
23.                I = I + 1
    
24.             Loop
    
25. 
    
26.         End If
    
27.     End If
    
28. Next
    
29. End If
    
30. End Sub

½Æ»s¥N½X