ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥»©«³Ì«á¥Ñ ©P¤j°¶ ©ó 2014-12-9 11:26 ½s¿è

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

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


    ¸Õ¸Õ¬Ý

½Æ¥» Xl0000229.rar (14.36 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-12-10 07:14 ½s¿è

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®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁ¼Ӥ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­Ì±Ð¾É, ÁÂÁÂ!!

TOP

¦^´_ 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

TOP

¦^´_ 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®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ ©P¤j°¶ ©ó 2014-12-10 18:46 ½s¿è

·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

TOP

¦^´_ 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®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD