Hsieh

¦b²M³æ½d³ò§äÅçÃҿﶵªÖ©w§äªº¨ì
§_«hÅçÃÒ´NµLªk³q¹L

GBKEE

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

Hsieh

1. Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   
2. Dim A As Range, c As Integer, s As Range
   
3. Application.EnableEvents = False
   
4. With Target.Validation
   
5. Set s = Evaluate(.Formula1)
   
6. End With
   
7. Set A = s.Find(Target, lookat:=xlWhole)
   
8. c = IIf(A.Column = A.CurrentRegion.Column, 1, -1)
   
9. Target.Offset(, c) = A.Offset(, c)
   
10. Application.EnableEvents = True
    
11. End Sub

½Æ»s¥N½X

infoverdad

¦^´_ 2# GBKEE

ÁÂÁÂGBKEEª©¤j,¯u¬O¤Ó´Î¤F!! ¥¦¤£¦ý§ó²¼ä,¤]¸Ñ¨M¤F§Ú¤§«e¦]À³¥Î»Ý­n,¶·³v¤@­×§ï©w¸q¦WºÙªº³Â·Ð!!
¤£¹L¬°ÁקK­×§ïÄæ¦W¡A¦Ó­P¥t¤@ÄæÄæ¦WÅܪťÕ, ÁÙ¬O­n¨Ï¥ÎexitHandler¨Ó³B²z¥¦.  ©Ò¥H§Ú§âµ{¦¡½Xµy·L­×§ï¤F¤@¤U,³o¼Ë§Ú¥¼¨Ó¦b¤u§@¤W´N¥i¥H¦h¦hµ½¥Î¥¦¤F!!

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     Dim wsLists As Worksheet, Rng As Range
   
4.     On Error GoTo errHandler
   
5. 
   
6.     If Target.Count > 1 Then Exit Sub
   
7.     Set wsLists = Worksheets("Lists")
   
8.     Application.EnableEvents = False
   
9.    
   
10.     If Target.Column = 2 Then
    
11.         Set Rng = wsLists.Range("A:A").Find(Target, LookAt:=xlWhole, MatchCase:=True)
    
12.         With Target.Offset(0, 1)
    
13.              If Not Rng Is Nothing Then .Value = Rng.Offset(0, 1).Value Else: GoTo exitHandler
    
14.         End With
    
15.         
    
16.     ElseIf Target.Column = 3 Then
    
17.         Set Rng = wsLists.Range("B:B").Find(Target, LookAt:=xlWhole, MatchCase:=True)
    
18.         With Target.Offset(0, -1)
    
19.              If Not Rng Is Nothing Then .Value = Rng.Offset(0, -1).Value Else: GoTo exitHandler
    
20.         End With
    
21.     End If
    
22.     Application.EnableEvents = True
    
23.    
    
24. exitHandler:
    
25.   Application.EnableEvents = True
    
26.   Exit Sub
    
27. 
    
28. errHandler:
    
29.   MsgBox Err.Number & ": " & Err.Description
    
30.   GoTo exitHandler
    
31.    
    
32. End Sub

½Æ»s¥N½X

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