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

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

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

¥»©«³Ì«á¥Ñ jackson7015 ©ó 2014-4-29 09:12 ½s¿è

½Ð°Ý¦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

¦^´_ 1# jackson7015

SHEET1ªºWorksheet_Change ¨Æ¥ó
  1. Run "SHEET1.Worksheet_Change", Sheet1.[A5]
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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ªº¦^ÂÐ

TOP

¦^´_ 3# jackson7015
§A¤£¬O­n¦b¨ä¥Lªº¥¨¶°¤¤,°õ¦æ³oµ{¦¡½X,¨Ó°õ¦æSheet1(¬d¸ß¥Îªí³æ)ªºWorksheet_Change¨Æ¥óµ{¦¡¶Ü?
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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Á¤£ºÉ..

TOP

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

TOP

¦^´_ 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¦¸ªº¦^ÂÐ

TOP

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

TOP

¦^´_ 6# GBKEE

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

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

TOP

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

TOP

        ÀR«ä¦Û¦b : µoµÊ®ð¬Oµu¼ÈªºµoºÆ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD