¥¦w
¥H¤Uªºµ{¦¡½X¬O§Ú¥Î¤gªk·Ò¿ûªº¤è¦¡°µªº
¥Ø«e¥¿±`¹B§@¤¤
½Ð°ª¤â¬Ý¬Ý¥i§_¾ã¦X ?
ÁÂÁÂ
Private Sub Worksheet_Change(ByVal Target As Range) '¥æ¤¬¬d¸ß
Application.Calculation = xlCalculationManual 'Ãö³¬¦Û°Ê¹Bºâ
ActiveSheet.Unprotect Password:=3551 'ºM®ø¤u§@ªí«OÅ@¨Ã¨ú®ø±K½X
Dim i%, x(), y(), xy()
Dim F1 As Range
Dim rn As Range, tt As Range
On Error Resume Next
Application.EnableEvents = False
With Sheets("¼t°Ó¸ê®Æ")
Set F1 = .Columns(1).Find([C3])
[c4] = .Cells(F1.Row, 2)
[c5] = .Cells(F1.Row, 7)
End With
Application.EnableEvents = True
Set T = Target
For Each tt In T
If tt.Column = 2 Then
Set rn = Sheet1.[c:c].Find(tt, , , 1)
If Not rn Is Nothing And tt.Offset(, 1) <> rn.Offset(, -1) Then
tt.Offset(, 1) = rn.Offset(, -1).Value
'tt.Offset(, 3) = rn.Offset(, 4).Value
' tt.Offset(, 6) = rn.Offset(, 1).Value
End If
ElseIf tt.Column = 3 Then
Set rn = Sheet1.[b:b].Find(tt, , , 1)
If Not rn Is Nothing And tt.Offset(, -1) <> rn.Offset(, 1) Then
tt.Offset(, -1) = rn.Offset(, 1).Value
' tt.Offset(, 2) = rn.Offset(, 5).Value
' tt.Offset(, 5) = rn.Offset(, 2).Value
End If
End If
Next tt
Set T = Nothing: Set tt = Nothing
Set U = Target
Set dbsheet = Sheets("¤Ñ®¦®Ñ¥Ø")
Set myrange = dbsheet.Range("c2:c2020")
For Each cell In U
rw = cell.Row '¦C
cl = cell.Column 'Äæ
Select Case cl
Case 2, 3
If cell = "" And rw > 6 Then '«~¦W³Q²MªÅ,¤£Åã¥Ü
Application.EnableEvents = False
Range(Cells(rw, 7), Cells(rw, 16)).ClearContents
Application.EnableEvents = True
ElseIf cell <> "" And rw > 6 And cl = 2 Then 'Åã¥Ü¸ê®Æ,§YÅã¥Ü
Set m = myrange.Find(cell, LookIn:=xlValues)
Application.EnableEvents = False
If Not m Is Nothing Then
rw2 = m.Row
Cells(rw, 8) = dbsheet.Cells(rw2, 12)
Cells(rw, 9) = dbsheet.Cells(rw2, 14)
Cells(rw, 7) = dbsheet.Cells(rw2, 15)
Cells(rw, 10) = dbsheet.Cells(rw2, 19)
Cells(rw, 11) = dbsheet.Cells(rw2, 8)
Cells(rw, 12) = dbsheet.Cells(rw2, 23)
Cells(rw, 13) = dbsheet.Cells(rw2, 24)
Cells(rw, 14) = dbsheet.Cells(rw2, 29)
Cells(rw, 15) = dbsheet.Cells(rw2, 10)
Cells(rw, 16) = dbsheet.Cells(rw2, 11)
End If
Set U = Nothing
Application.EnableEvents = True
End If
Case 7, 8, 9, 10, 11, 12, 13, 14, 15, 16
If rw > 6 Then 'קï¸ê®Æ
Set m = myrange.Find(Cells(rw, 2), LookIn:=xlValues)
Application.EnableEvents = False
If Not m Is Nothing Then
rw2 = m.Row
dbsheet.Cells(rw2, 12) = Cells(rw, 8)
dbsheet.Cells(rw2, 14) = Cells(rw, 9)
dbsheet.Cells(rw2, 15) = Cells(rw, 7)
dbsheet.Cells(rw2, 19) = Cells(rw, 10)
dbsheet.Cells(rw2, 8) = Cells(rw, 11)
dbsheet.Cells(rw2, 23) = Cells(rw, 12)
dbsheet.Cells(rw2, 24) = Cells(rw, 13)
dbsheet.Cells(rw2, 29) = Cells(rw, 14)
dbsheet.Cells(rw2, 10) = Cells(rw, 15)
dbsheet.Cells(rw2, 11) = Cells(rw, 16)
End If
dbsheet.Close
myrange.Quit
Set U = Nothing
Set dbsheet = Nothing
Set myrange = Nothing
Application.EnableEvents = True
End If
End Select
Next cell
On Error GoTo 1
Set V = Target
If V.Count > 1 Then End
If V.Address Like "*$A$2*" Then
x = Array("A", "B", "C")
y = Array("Aܮw", "Bܮw", "Cܮw")
If V = "¶i ³f ³æ" Or V = "ÃØ ®Ñ ³æ" Or V = "µo ¦æ ªÌ ¨ú ®Ñ ³æ" Or V = "¨ú ³f ³æ" Then
xy = x
Else
xy = y
End If
With [E7:E32].Validation
.Delete
.Add Type:=3, AlertStyle:=1, Operator:=1, Formula1:=Join(xy, ",")
End With
End If
dbsheet.Close
myrange.Quit
Set V = Nothing
Set dbsheet = Nothing
Set myrange = Nothing
Application.Calculate '«ì´_¦Û°Ê¹Bºâ
Application.Calculation = xlCalculationAutomatic '«ì´_¦Û°Ê¹Bºâ
Application.StatusBar = ´Nºü
Application.EnableEvents = True
1:
Set dbsheet = Nothing
Set myrange = Nothing
Application.Calculate '«ì´_¦Û°Ê¹Bºâ
Application.Calculation = xlCalculationAutomatic '«ì´_¦Û°Ê¹Bºâ
Application.StatusBar = ´Nºü
Application.EnableEvents = True
End Sub |