²Å¦X¨âµ§¸ê®Æ¦Û¦æÅã¥Ü¦a°Ï

©P¤j°¶

«e½ú­Ì, ¦­±á
¤p§Ì¦³¤@°ÝÃD, ½Ð¦U«e½ú¨ó§U, °ÝÃD¬O¤@­Ó²Å¦X¨âµ§¸ê®Æ¦Û¦æ¶Ç¦^¦a°Ï, ²{¤p§Ì§âÀɮפW¶Ç, ¥ý¦æÁ¹L«e½ú­Ì,
¯¬Ä@§Ö¼Ö, ÁÂ!!
¬¡­¶Ã¯1.rar (9.52 KB)

¤U¸ü¦¸¼Æ: 19

2014-12-9 11:26

owen06

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


    ¸Õ¸Õ¬Ý

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

©P¤j°¶

ÁÂÁ¼ӤW¨â¦ì¤j¤j,  ·Q½Ð±Ð¤@­Ó°ÝÃD,
¤@¯ë¸ê®Æ«ö¦Û¦æÅã¥Ü³£·|¥Î¤W¤U¦C«ü¥O,
Private Sub Worksheet_Change(ByVal Target As Range)
¥i§_·|¦³¨ä¥L¶}»yªk¨ú¥N³o¥y»yªk, ©Î¦³¬Û¦P®ÄªG, ¦]¤p§Ì¥»¨­¤u§@ªí¤w¦³µ{¦¡, ¦Ó¶}ÀY²Ä¤@¥y«K¬O³o¥y,
¬Gµ{¦¡«K¥X²{­«½Æ«ü¥O, ·Ð½Ð¤j¤j­Ì±Ð¾É, ÁÂÁÂ!!

owen06

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


    ¬JµM§Aªºµ{¦¡¬O¥Î Private Sub Worksheet_Change(ByVal Target As Range)¶}ÀY¡A
    ¨ºª½±µ±N»yªk¤º®e¥[¤J§Aªºµ{¦¡¤U­±¤£¦æ¶Ü¡HÁÙ¬O¤è«K§âµ{¦¡¤º®e¶K¤W¨Ó¬Ý¬Ý¶Ü¡H

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

©P¤j°¶

·PÁ¨â¦ì¼Ó¥D¦^À³, ¥ý¦æÁ¹L,
¤p§Ì¯uªº¨Sªk§â²{¨Ï¥Îªºµ{¦¡»P¨â¦ì¤j¤j©Ò¨Ñªºµ{¦¡¿Ä¦X, ²{§â¨Ï¥Î¶}ªºµ{¦¡¶K¤W, ½Ð¨â¦ì¼Ó¥D¨ó§U, ÁÂÁÂ!!
Private Sub Worksheet_Change(ByVal T As Range)
Dim Rng As Range, A As Range, Dha As Workbook, pp
Application.EnableEvents = False
Set Dha = Workbooks("sss­Ü®w¸ê®Æ.xlsm")
Set Rng = Dha.Sheets(1).UsedRange
Select Case T.Column
Case 2
   If IsDate(T) Then T.Offset(, -1) = Month(T)
Case 3
   Set A = Rng.Columns(2).Find(T, lookat:=xlWhole)
   If A Is Nothing Then MsgBox "µL¦¹³fª«½s¸¹": GoTo 10
   T.Offset(, 1) = A.Offset(, 1)
   T.Offset(, 2) = A.Offset(, 2)
   T.Offset(, 3) = A.Offset(, 3)
   T.Offset(, 5) = A.Offset(, 4)
Case 7
    Set A = Rng.Columns(2).Find(T.Offset(, -4), lookat:=xlWhole)
    pp = Application.SumIf(Range("C:G"), A, Range("G:G")) '­pºâ­Ü®w´£¨úÁ`¼Æ
    If pp > (A.Offset(, 8) + A.Offset(, 10)) Then
        MsgBox T.Offset(, -3) & "¦s¶q¤£¨¬­«·s¶ñ¼g"
        T.Interior.ColorIndex = 26
        GoTo 10
    End If
    T.Interior.ColorIndex = xlNone
    pp = Application.SumIf(Range("C:G"), T.Offset(, -4), Range("G:G")) '­pºâ­Ü®w´£¨úÁ`¼Æ
    A.Offset(, 8) = pp
    A.Offset(, 10) = A.Offset(, 7) + A.Offset(, 9) - A.Offset(, 8)
    T.Offset(, 2) = T * T.Offset(, 1)
    Dha.Save
End Select
10
Application.EnableEvents = True
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal T As Range, Cancel As Boolean)
    Dim A As Range
    If Not Application.Intersect(T, Range("A3:L" & Application.CountA([A3:A6]) + 2)) Is Nothing Then
        Cancel = True
        With Workbooks("sss­Ü®w¸ê®Æ.xlsm")
            Set A = .Sheets(1).UsedRange.Columns(2).Find(Range("C" & T.Row), lookat:=xlWhole)
            If A.Column <> 2 Or A = "" Then
                MsgBox "¸ê®Æ¤£¥¿½T ½Ð¬d©ú"
                Range(Cells(T.Row, "A"), Cells(T.Row, "L")).Select
                Exit Sub
            End If
            If MsgBox(Range("C" & T.Row) & " " & Range("D" & T.Row) & Chr(10) _
                    & "¨ú®ø " & [G2] & " " & Range("G" & T.Row) & Chr(10) & "½Ðª`·N [¥»¬ö¿ý] ¨ú®ø«áµLªk´_­ì", vbYesNo) = vbYes Then
                Application.ScreenUpdating = False
                Application.EnableEvents = False
                A.Offset(, 8) = Application.SumIf(Range("C:C"), A, Range("G:G")) - Range("G" & T.Row) 'Åܧó¤J³fÁ`¼Æ©ó¸ê®Æ®w
                A.Offset(, 10) = A.Offset(, 7) + A.Offset(, 9) - A.Offset(, 8)
                .Save
                Range(Cells(T.Row, "A"), Cells(T.Row, "L")).Delete (3)
                Application.EnableEvents = True
                ActiveWorkbook.Save
                Application.ScreenUpdating = True
            End If
        End With
    End If
End Sub

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

©P¤j°¶

¦^´_ 8# GBKEE
ÁÂÁ¼ӥD±Ð¾É, ¤w¦¨¥\, ·PÁ¤j¤j, ¯¬§Ö¼Ö,
ÁÂÁÂ!!