GBKEE

¦^´_ 1# infoverdad
¤£¿ùªº¤À¨É,¥t¥Î Find ¤èªk  ¤À¨É.

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim wsLists As Worksheet, Rng As Range
   
3.     If Target.Count > 1 Then Exit Sub
   
4.     Set wsLists = Worksheets("Lists")
   
5.     Application.EnableEvents = False
   
6.     If Target.Column = 2 Then
   
7.         Set Rng = wsLists.Range("A:A").Find(Target, LookAt:=xlWhole, MatchCase:=True)
   
8.         With Target.Offset(0, 1)
   
9.             If Not Rng Is Nothing Then .Value = Rng.Offset(0, 1).Value Else .Value = ""
   
10.         End With
    
11.     ElseIf Target.Column = 3 Then
    
12.         Set Rng = wsLists.Range("B:B").Find(Target, LookAt:=xlWhole, MatchCase:=True)
    
13.         With Target.Offset(0, -1)
    
14.             If Not Rng Is Nothing Then .Value = Rng.Offset(0, -1).Value Else .Value = ""
    
15.         End With
    
16.     End If
    
17.     Application.EnableEvents = True
    
18. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 4# Hsieh¶Wª©
§Aªºµ{¦¡§ó²¼ä  
¦ý Set A = s.Find(Target, lookat:=xlWhole) ¤§«á¨S¦³³]¤U A Is Nothing ªº±ø¥ó,(³oÀɮתºÅçÃÒ²M³æ½d³ò¤¤¨S¦³ªÅ¥Õ­È)

GBKEE

¦^´_ 6# Hsieh¶Wª©
§Úªº·N«ä¬O¦³ÅçÃÒ²M³æªºÀx¦s®æ¦¡¬O¤¹³\¿é¤JªÅ¥Õ­Èªº,¦p¿é¤J­È¬O ªÅ¥Õ­È,
Set A = s.Find(Target, lookat:=xlWhole) ¶Ç¦^ A Is Nothing
§A4¼Óªºµ{¦¡, ¦b¼Ó¥DªºÀÉ®×·|²£¥Í¿ù»~.

GBKEE

¦^´_ 17# yen956
°õ¦æEx¬Ý¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     ActiveSheet.[A1:A10] = ""
   
4. End Sub
   
5. Private Sub Worksheet_Change(ByVal Target As Range)
   
6.     'Target ªº«¬ºA¬O Range (¥¦¬O¤@­ÓÀx¦s®æ©ÎÀx¦s®æ½d³ò)
   
7.     MsgBox Target.Cells.Count
   
8. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 21# dryadf
1. ¦b±zfind¤èªk¤º¬O¼gif Target.Count > 1 Then Exit sub
   A: ¨S¦³Target.Count·|¤p©ó1ªº,  ¬°ÁקK¦p¤UÃþ¦ü¦p¤U±¡ªpµo¥Í.¸ÕµÛ Find¤èªk¤º ¥h±¼³o¬q¬Ý¬Ý if Target.Count > 1 Then Exit sub

1. Option Explicit
   
2. Sub Ex()
   
3.     ActiveSheet.[A1:A10] = ""
   
4. End Sub
   
5. Private Sub Worksheet_Change(ByVal Target As Range)
   
6.     Dim E As Range
   
7.     For Each E In Target
   
8.         MsgBox E.Address
   
9.     Next
   
10. End Sub

½Æ»s¥N½X

2. ¦b°õ¦æ¹L±z¤W­±¤À¨ÉªºEx¤§«á¡A§Ú¸ÕµÛ¶Ã§ï¸Ì­±ªºµ{¦¡¬Ý¦³¤°»ò®ÄªG¡C
¼g¦¨<1ªº®É­Ô¤£¬O¸Ó¥Nªí§Ú¿ï¾Ü¤@­Ó¥H¤WªºÀx¦s®æ®É¡A´N¯àÅã¥Ü¥¿±`ªºµ²ªG¶Ü?q
A:¬O>=1§a,¥Nªí§Ú¿ï¾Ü¤@­Ó¥H¤WªºÀx¦s®æ.

3. ±z¦bEx³o¬qµ{¦¡¤¤¥Î¨ì¤FOption Explicit
¦pA 1 ¤¤¨SDim E As Range ¨t²Î ·|³qª¾§A­n«Å§iÅܼÆ.

GBKEE

¥i¬OExit Sub¤£¬O«üÂ÷¶}³o­Ó¨Æ¥ó¶Ü?  ³o¼Ë¾ã¥y¸ÑÄÀ¤U¨Ó¤£´N¬Oµ¥©ó¿ï¾Ü¦Ü¤Ö¤@­Ó¥H¤WªºÀx¦s®æ´NÂ÷¶}³o­Ó¨Æ¥ó?
dryadf µoªí©ó 2014/5/5 01:08

¨S¿ùªº

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. 'ByVal ¿ï¾Ü©Ê¤Þ¼Æ¡Cªí¥Ü¥H¶Ç­Èªº¤è¦¡¨Ó¶Ç»¼¤Þ¼Æ¡C
   
3. 'Target : ÅܼƦWºÙ
   
4. 'As Range : «¬ºA¬° Rangeª«¥ó
   
5. ' Range ª«¥ó¡A¸Óª«¥ó¥Nªí¤@­ÓÀx¦s®æ©ÎÀx¦s®æ½d³ò

½Æ»s¥N½X