|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Private Sub Worksheet_Change(ByVal T As Range)
If T.Row > 3 And T.Column = 2 Then
If T.Count > 1 Then End
If T.Value = "" Then Cells(T.Row, 3).Resize(1, 14) = Empty
xm = T.Value
With Sheets("工人信息数据库")
r = .Cells(Rows.Count, 2).End(xlUp).Row
ar = .Range("a1:j" & r)
End With
w = T.Row
For i = 2 To UBound(ar)
If ar(i, 2) = xm Then
Cells(w, 3) = ar(i, 3)
Cells(w, 4) = ar(i, 10)
Cells(w, 5) = "技工"
Cells(w, 11) = ar(i, 8)
Cells(w, 12) = ar(i, 7)
Exit For
End If
Next i
With Sheets("考勤表")
r = .Cells(Rows.Count, 2).End(xlUp).Row
ar = .Range("a3:ah" & r)
End With
For i = 3 To UBound(ar)
If ar(i, 2) = xm Then
Cells(w, 6) = ar(i, UBound(ar, 2))
Exit For
End If
Next i
End If
If T.Row > 3 And T.Column = 7 Then
If T.Count > 1 Then End
If T.Value = "" Then End
If T.Offset(, -1) = "" Then End
T.Offset(, 1) = T.Offset(, -1) * T.Value
If T.Offset(, 2) <> "" Then T.Offset(, 3) = T.Offset(, 1) - T.Offset(, 2)
End If
If T.Row > 3 And T.Column = 9 Then
If T.Count > 1 Then End
If T.Value = "" Then End
If T.Offset(, -1) = "" Then End
T.Offset(, 1) = T.Offset(, -1) - T.Value
If T.Offset(, -2) <> "" And T.Offset(, -3) <> "" Then T.Offset(, -1) = T.Offset(, -2) * T.Offset(, -3)
End If
End Sub
|
|