[Visual Basic] 纯文本查看 复制代码 Sub TaoCVmoi()
'
' TaoCVmoi Macro
' Tao Cong viec moi, tu ghi cong thuc
' Keyboard Shortcut: Ctrl+Shift+J
'
Range("D" & ActiveCell.Row).Select
With ActiveCell
.FormulaR1C1 = "Cap nhat cong viec"
.Offset(0, -3).FormulaR1C1 = "=IF(RC[3]<>0,MAX(R6C1:R[-1]C)+1,"""")"
.Offset(1, 1).FormulaR1C1 = "=DATEVALUE(TEXT(NOW(),""dd/mm/yyyy""))"
.Offset(1, 1) = .Offset(1, 1).Value
.Offset(0, 1).Resize(1, 4).FormulaR1C1 = "=IF(MAX(R[1]C:R[4]C)=0,"""",MAX(R[1]C:R[4]C))"
.Offset(0, 5).Resize(1, 2).FormulaR1C1 = "=SUBTOTAL(9,R[1]C:R[4]C)"
.Offset(0, 7).FormulaR1C1 = "=VLOOKUP(MAX(R[1]C[-6]:R[4]C[-6]),R[1]C[-6]:R[4]C,7)"
.Offset(0, 9).FormulaR1C1 = _
"=IF(AND(RC[-9]<>0,RC[-9]<>""Cap nhat cong viec""),RC[-2],"""")"
.Offset(1, 7) = Range("M1").FormulaR1C1
.Offset(2, 7) = Range("M2").FormulaR1C1
.Offset(3, 7) = Range("M3").FormulaR1C1
.Offset(4, 7) = Range("M4").FormulaR1C1
.Offset(1, 2).Resize(4, 1).FormulaR1C1 = "=RC[-1]+7"
.Offset(1, 3).Resize(4, 2).FormulaR1C1 = "=RC[-2]"
.Offset(1, 5).Resize(4, 1).FormulaR1C1 = "=IF(RC[-1]<>0,RC[-1]-RC[-4]+1,"""")"
End With
End Sub
Sub XoaCV()
'
' XoaCV Macro
' Xoa Cong viec va cong thuc
' Keyboard Shortcut: Ctrl+Shift+X
'
Range("D" & ActiveCell.Row).Select
With ActiveCell
If .FormulaR1C1 <> "" Then
.Offset(0, -3).Resize(5, 13).FormulaR1C1 = ""
Else
MsgBox "Ban chua chon dung cong viec de Xoa"
End If
End With
End Sub |