[µo°Ý] ¦p¦ó±NWorksheet_ChangeªºÅܼƫŧi¡A§ï¦¨¦b¤@¯ë¼Ò²Õ¨Ï¥Î?

jackson7015

½Ð°Ý¦U¦ì«e½ú
¦p¦ó±N¤U­±Worksheet¤¤ªºÅܼƫŧi¡A§ï¦¨¦b¼Ò²Õªº¤@¯ë¥¨¶°´N¦n¤F?

¦]¬°°µ¤F¨ä¥L¥¨¶°­n¨Ï¥Î
¦ý¬O¥u­n¦³§ó°Ê¨ìWorksheetªº³æ®æÀx¦s®æ¡A´N·|±Ò°Ê¦¹«Å§i

·Q½Ð±Ð«e½ú­Ì¡A¦p¦ó±N¥H¤UÅܼƫŧi¡A§ï¦¨¤@¯ë¼Ò²Õªº¥¨¶°¨Ï¥Î¥B¥u§@¥Î¦b[a5]Àx¦s®æ´N¦n¤F
·PÁ¤£§[«ü±Ð~

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim A As Range, Rng As Range
   
3. If Target.Column = 1 Then
   
4. With Sheets("ºî¦X¸ê®Æ®w")
   
5. For i = 1 To .UsedRange.Rows.Count
   
6.    Set A = .UsedRange.Rows(i).Find(Target)
   
7.    If Not A Is Nothing Then
   
8.      If Rng Is Nothing Then
   
9.      Set Rng = .UsedRange.Rows(i)
   
10.      Else
    
11.      Set Rng = Union(Rng, .UsedRange.Rows(i))
    
12.      End If
    
13.     End If
    
14. Next
    
15. End With
    
16. End If
    
17. Application.EnableEvents = False
    
18.     If Not Rng Is Nothing Then
    
19.     Rng.Copy: Target.Offset(, 1).PasteSpecial 3
    
20.     Else
    
21.     Target.Offset(, 1).Resize(, 50) = ""
    
22.     End If
    
23. Application.EnableEvents = True
    
24.     MsgBox "¬d¸ßµ²§ô"
    
25. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 1# jackson7015

SHEET1ªºWorksheet_Change ¨Æ¥ó

1. Run "SHEET1.Worksheet_Change", Sheet1.[A5]

½Æ»s¥N½X

jackson7015

¦^´_ 2# GBKEE

GBKEEª©¤j±z¦n¡F
½Ð°Ý¬O§_¥u·s¼W¤@²Õ¦¹¥¨¶°±N­ì¥»ªºÅܼƫŧi¾É¤J¡AµM«á¦A±N±zªº«Å§i©ñ¨ìSHEET1(¬d¸ß¥Îªí³æ)ªºWorksheet_Change¤º?

¦ý¬O§Ú¦p¦¹°µ·|«Å§i¿ù»~
¤£¾å±o¬O­þ­Ó¨BÆJ¿ù»~¤F?

·PÁÂGBKEEª©¤jªº¦^ÂÐ

GBKEE

¦^´_ 3# jackson7015
§A¤£¬O­n¦b¨ä¥Lªº¥¨¶°¤¤,°õ¦æ³oµ{¦¡½X,¨Ó°õ¦æSheet1(¬d¸ß¥Îªí³æ)ªºWorksheet_Change¨Æ¥óµ{¦¡¶Ü?

jackson7015

¦^´_ 4# GBKEE

GBKEEª©¤j¡A¹ï¤£°_¡A¨S¦³³¯­z²M·¡»Ý­nªº³¡¤À

«Å§i¦bWorksheet_Changeªº³¡¤À¡A·|¤U¦Cªº¥¨¶°±Ò°Ê¡AÀx¦s®æ¥u­n¤@ÅÜ°Ê¡A´N·|¦Û°Ê±Ò°Ê«Å§i³¡¤À

1. Sub ²M°£¬d¸ßªí®æ()
   
2. ' ²M°£¬d¸ßªí®æ ¥¨¶°
   
3. With Sheets("¬d¸ß¥Îªí³æ")
   
4.     Range("$B5:$BR301").Select
   
5.     Selection.ClearContents
   
6.     Range("A5").Select
   
7.     End With
   
8. End Sub

½Æ»s¥N½X

§Ú·Q§ï¦¨¥u¥Î¥¨¶°¤â°Ê¥h·j¯Á¡A³o¼Ë¤~¤£·|¦³Àx¦s®æ¦³Åܰʪº®É­Ô¡A´N±Ò°ÊWorksheet_Changeªº³¡¤À
¬O§_¯à±NWorksheet_Changeµ{¦¡½X¡A§ï¦¨¦bModule1¼Ò²Õªº½sµ{
¦Û¤v¬ã¨s¤F´X¤Ñ¡AÁÙ¬O¤£·|§â¦bWorksheet_Changeªº½s¼g¡A§ï¦¨¦bModule1¼Ò²Õ¤¤...

¦Û¤v¸ê½è·M¶w¡A·Q³Â·ÐGBKEEª©¤j¯à§_À°¦£­×¥¿½s¼g
·PÁ¤£ºÉ..

GBKEE

¦^´_ 5# jackson7015
¬O³o¼Ë¶Ü?

1. Sub ²M°£¬d¸ßªí®æ()
   
2. ' ²M°£¬d¸ßªí®æ ¥¨¶°
   
3.     With Sheets("¬d¸ß¥Îªí³æ")
   
4.         .Range("$B5:$BR301").ClearContents
   
5.         .Range("A5").Select
   
6.         Run "Module1.Worksheet_Change", .[A5]
   
7.         '§A¤w±N Worksheet_Changeªº½s¼g¡A§ï¦¨¦bModule1¼Ò²Õ
   
8.     End With
   
9. End Sub

½Æ»s¥N½X

jackson7015

¦^´_ 6# GBKEE

GBKEEª©¤j¡A·Q¦A¦¸³Â·Ð¤@¤U
¦]¬°§Úªºªí¹F¦n¹³¦³°ÝÃD..

§Ú·Qª½±µ±N1¼Óªºµ{¦¡½X
¤£¦bWorksheet¤º½s¼g¡A¦Ó¬O½s¼g¦bModule

²{¦bªº°ÝÃD¬O¥u­n¤@§ïÅÜÀx¦s®æ[A5]ªº¤º®e¡A´N·|¦Û°Ê°õ¦æ1¼Óªºµ{¦¡½X
§Ú·Q§ï¦¨¤â°Ê¤è¦¡°õ¦æ¡A¤â°Ê°õ¦æ Sub ¬d¸ßªí®æ() ¥¨¶°¤~·|°õ¦æ

¤£¾å±o³o¼Ë¸Ñ¡AGBKEEª©¤j¬O§_¤ñ¸û¦n²z¸Ñ
·PÁ±z¦A¦¸ªº¦^ÂÐ

jackson7015

¦^´_  GBKEE
ªþ¤WÀɮסA¦A½ÐGBKEEª©¤j¬Ý¬Ý
°Ñ¦Ò¸ê®Æ®w.rar (992.52 KB)

¤U¸ü¦¸¼Æ: 9

2014-5-5 16:09

jackson7015

¦^´_ 6# GBKEE

Àɮפwªþ¤W¡A¦A½ÐGBKEE¤j¤j¬Ý¬Ý¬O§_¥i¦æ

¤µ¤Ñ¬ã¨s¤F¥b¤ÑÁÙ¬O¤£·|§ï...

GBKEE

¦^´_ 9# jackson7015
¸Õ¸Õ¬Ý

1. '³£¬OModule3¤Wªºµ{¦¡½X
   
2. Sub ¬d¸ß¸ê®Æ()
   
3.    ' Worksheet_Change [A5]   '¥i¥H³o¼Ë°µ
   
4.     Ex
   
5. End Sub
   
6. 'Sheets("¬d¸ß¥Îªí³æ")ªºWorksheet_Change¨Æ¥ó,§A¬O·Q·h²¾¨ìModule3¼Ò²Õ¤W
   
7. Private Sub Worksheet_Change(ByVal Target As Range)
   
8. Dim A As Range, Rng As Range
   
9. If Target.Column = 1 Then
   
10. With Sheets("ºî¦X¸ê®Æ®w")
    
11. For i = 1 To .UsedRange.Rows.Count
    
12.    Set A = .UsedRange.Rows(i).Find(Target)
    
13.    If Not A Is Nothing Then
    
14.      If Rng Is Nothing Then
    
15.      Set Rng = .UsedRange.Rows(i)
    
16.      Else
    
17.      Set Rng = Union(Rng, .UsedRange.Rows(i))
    
18.      End If
    
19.     End If
    
20. Next
    
21. End With
    
22. End If
    
23. Application.EnableEvents = False
    
24.     If Not Rng Is Nothing Then
    
25.     Rng.Copy: Target.Offset(, 1).PasteSpecial 3
    
26.     Else
    
27.     Target.Offset(, 1).Resize(, 50) = ""
    
28.     End If
    
29. Application.EnableEvents = True
    
30.     MsgBox "¬d¸ßµ²§ô"
    
31. End Sub
    
32. Private Sub Ex()
    
33.     Dim F As Range, AD As String, Rng As Range, xRng As Range
    
34.     Set xRng = Sheets("¬d¸ß¥Îªí³æ").[A5]
    
35.     With Sheets("ºî¦X¸ê®Æ®w").UsedRange
    
36.         Set F = .Find(xRng, LOOKAT:=xlPart)
    
37.         If Not F Is Nothing Then AD = F.Address
    
38.         Do While Not F Is Nothing
    
39.             If Rng Is Nothing Then
    
40.                 Set Rng = .Rows(F.Row)
    
41.             Else
    
42.                 Set Rng = Union(Rng, .Rows(F.Row))
    
43.             End If
    
44.             Set F = .FindNext(F)
    
45.             If F.Address = AD Then Exit Do
    
46.         Loop
    
47.     End With
    
48.     If Not Rng Is Nothing Then
    
49.         Rng.Copy xRng.Offset(, 1)
    
50.         MsgBox "¬d¸ßµ²§ô"
    
51.     Else
    
52.         xRng.Offset(, 1).Resize(, 50) = ""
    
53.     End If
    
54. End Sub

½Æ»s¥N½X