GBKEE

Function(¦Û­q¨ç¼Æ)

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     With Target
   
4.         If (.Column = 3 Or .Column = 10) And .Row >= 4 Then
   
5.             Cells(.Row, "q") = EX_¦a°Ï(Cells(.Row, "C") & "," & Cells(.Row, "J")& ",")
   
6.          End If
   
7.     End With
   
8. End Sub
   
9. Private Function EX_¦a°Ï(Msg As String) As String
   
10.     Dim AR, A, i
    
11.     EX_¦a°Ï = ""
    
12.     AR = Sheets("¤u§@ªí2").Range("A1").CurrentRegion
    
13.     AR = Application.Transpose(Application.Transpose(AR))
    
14.     For i = 1 To UBound(AR)
    
15.         A = Application.WorksheetFunction.Index(AR, i)
    
16.         If InStr(UCase(Join(A, ",")), UCase(Msg)) = 1 Then
    
17.             EX_¦a°Ï = A(3)
    
18.             Exit For
    
19.         End If
    
20.     Next
    
21. End Function

½Æ»s¥N½X

GBKEE

¦^´_ 4# ©P¤j°¶

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     '¨ä¥Lµ{¦¡½X
   
4.     '¨ä¥Lµ{¦¡½X
   
5.     Ex Target
   
6.     '¨ä¥Lµ{¦¡½X
   
7.     '¨ä¥Lµ{¦¡½X
   
8. End Sub
   
9. Private Sub Ex(T As Range)
   
10.     Application.EnableEvents = False
    
11.     With T
    
12.         If (.Column = 3 Or .Column = 10) And .Row >= 4 Then
    
13.             Cells(.Row, "q") = EX_¦a°Ï(Cells(.Row, "C") & "," & Cells(.Row, "J") & ",")
    
14.         End If
    
15.     End With
    
16.     Application.EnableEvents = True
    
17. End Sub
    
18. Private Function EX_¦a°Ï(Msg As String) As String
    
19.     Dim AR, A, i
    
20.     EX_¦a°Ï = ""
    
21.     AR = Sheets("¤u§@ªí2").Range("A1").CurrentRegion
    
22.     AR = Application.Transpose(Application.Transpose(AR))
    
23.     For i = 1 To UBound(AR)
    
24.         A = Application.WorksheetFunction.Index(AR, i)
    
25.         If InStr(UCase(Join(A, ",")), UCase(Msg)) = 1 Then
    
26.             EX_¦a°Ï = A(3)
    
27.             Exit For
    
28.         End If
    
29.     Next
    
30. End Function

½Æ»s¥N½X

GBKEE

¦^´_ 7# ©P¤j°¶
½Ð±N§A­ì¥»ªº Private Sub Worksheet_Change(ByVal T As Range)
½Æ»s¦b¤u§@ªíªº¼Ò²Õ¤W,­×§ïµ{§Ç¦WºÙ¬°
¨Ò Private Sub Ex_Sub1(ByVal T As Range)

­ì¥»ªº Private Sub Worksheet_Change(ByVal T As Range)¨Æ¥ó
­×§ï¤º®e¦p¤U

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     Ex Target        'Target: ­n¶Ç»¼µ¹³o°Æµ{¦¡ªºÅܼÆ
   
4.    Ex_Sub1 Target
   
5.     '¨ä¥Lµ{¦¡½X
   
6.    '¨ä¥Lµ{¦¡½X
   
7. End Sub

½Æ»s¥N½X