[µo°Ý] ½Ð¦U¦ì°ª¤â¨ó§U,ÁÂÁ±z

usana642

·Q½Ð¦U¦ì°ª¤â¨ó§U,»Ý¨D¦p¤U:
§Ú·Q¼g¤@­Ó¦³Ãö©ó¨C¤éµæ³æ³]­pªí,§÷®Æ¦³a,b,c,d,e...µ¥¼ÆºØ,¨C¤Ñ¦UºØ§÷®Æ¥[Á`¬°15
¨Ò¦p
         A     B C D E F G H I J K -Äæ
¦C1  NO.1  a 2 c  2 b 2  e 2 f 3  
¦C2  NO.2  c 2 b  2 f 3  e 2 a 2  -->·í§Ú³o¤@¦C¿é¤J§¹¦¨«á,·|©MNO.1ªº¸ê®Æ¤ñ¹ï,·|¸õ¥Xĵ§i»¡µæ³æ¤w­«½Æ
¦C3  NO.3  c 2 b  2 f 3  e 2 a 2  -->·í§Ú³o¤@¦C¿é¤J§¹¦¨«á,·|©MNO.1©MNO.2ªº¸ê®Æ¤ñ¹ï,µL­«½Æ,«h¤£¥²¸õ¥Xĵ§i

¥H¤W³o¬O§Ú·Q­n§¹¦¨ªºµæ³æ,¥i¥H¦bSheet1¿é¤J¸ê®Æ,¦A©MSheet2ªº¾ú¥vµæ³æ¤ñ¹ï,Àµ½Ð¦U¦ì°ª¤âÀ°§Ú³o­Ó¤j¦£,ÁÂÁ±z

usana642

¦^´_ 5# Hsieh


    ÁÂÁ¶W¯Åª©¥Dªº¨ó§U,ÁÂÁ±z

Hsieh

¦^´_ 1# usana642

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Application.CountA(Cells(Target.Row, 2).Resize(, 10)) <> 10 Or Target.Row < 2 Or Target.Column > 11 Then Exit Sub
   
3.    For i = 1 To Target.Row - 1
   
4.    k = 0
   
5.       For j = 2 To 11
   
6.       k = k + IIf(Application.CountIf(Cells(Target.Row, 2).Resize(, 10), Cells(i, j)) = 1, 1, 0)
   
7.       Next
   
8.       If k = 10 Then MsgBox "»P²Ä" & i & "¦C­«½Æ": Exit Sub
   
9.    Next
   
10. End Sub

½Æ»s¥N½X

usana642

¦^´_ 2# register313


    ¥i¥H¥¿±`°õ¦æ¤F,ÁÂÁ±z

usana642

¦^´_ 2# register313


    ÁÂÁÂregister313¼ö¤ß¨ó§U,§Ú¥ý¸Õ¬Ý¬Ý,ÁÂÁ±z

register313

¦^´_ 1# usana642

¤u§@ªí¼Ò²Õ

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Column > 11 Then Exit Sub
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. LR = [A65536].End(xlUp).Row
   
5. For I = 1 To LR - 1
   
6.    For J = 2 To 10 Step 2
   
7.      d(Cells(I, J).Value) = ""
   
8.    Next J
   
9.    If d.exists(Cells(LR, 2).Value) And d.exists(Cells(LR, 4).Value) And d.exists(Cells(LR, 6).Value) And d.exists(Cells(LR, 8).Value) And d.exists(Cells(LR, 10).Value) Then
   
10.      MsgBox "»P²Ä" & I & "¦C­«ÂÐ"
    
11.    End If
    
12.    d.RemoveAll
    
13. Next I
    
14. End Sub

½Æ»s¥N½X