Code:
Private Sub UserForm_Initialize()
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox1.SetFocus
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub EnterData_Click()
Dim ws As Worksheet
Set ws = Worksheets("Depreciation")
'Insert Total Hours
ws.Cells(14, 4).Value = TextBox1.Value
ws.Cells(15, 4).Value = TextBox1.Value
ws.Cells(16, 4).Value = TextBox2.Value
ws.Cells(17, 4).Value = TextBox3.Value
ws.Cells(18, 4).Value = TextBox4.Value
ws.Cells(19, 4).Value = TextBox5.Value
'Insert Year Headers
ws.Cells(14, 3).Formula = "=IF(D14="""", """", ""Year 1"")"
ws.Cells(15, 3).Formula = "=IF(D15="""", """", ""Year 1"")"
ws.Cells(16, 3).Formula = "=IF(D16="""", """", ""Year 2"")"
ws.Cells(17, 3).Formula = "=IF(D17="""", """", ""Year 3"")"
ws.Cells(18, 3).Formula = "=IF(D18="""", """", ""Year 4"")"
ws.Cells(19, 3).Formula = "=IF(D18="""", """", ""Year 5"")"
'Insert Hourly Rate
ws.Cells(14, 5).Formula = "=$D$10"
ws.Cells(15, 5).Formula = "=$D$10"
ws.Cells(16, 5).Formula = "=$D$10"
ws.Cells(17, 5).Formula = "=$D$10"
ws.Cells(18, 5).Formula = "=$D$10"
ws.Cells(19, 5).Formula = "=$D$10"
'Insert Depreciation Rate
ws.Cells(14, 6).Formula = "=D14*E14"
ws.Cells(15, 6).Formula = "=D15*E15"
ws.Cells(16, 6).Formula = "=D16*E16"
ws.Cells(17, 6).Formula = "=D17*E17"
ws.Cells(18, 6).Formula = "=D18*E18"
ws.Cells(19, 6).Formula = "=D19*E19"
'Insert Accumulated Depreciation
ws.Cells(14, 7).Formula = "=F14"
ws.Cells(15, 7).Formula = "=F14"
ws.Cells(16, 7).Formula = "=G15+F16"
ws.Cells(17, 7).Formula = "=G16+F17"
ws.Cells(18, 7).Formula = "=G17+F18"
ws.Cells(19, 7).Formula = "=G18+F19"
'New Value
ws.Cells(14, 8).Formula = "=$D$5-F14"
ws.Cells(15, 8).Formula = "=$D$5-F15"
ws.Cells(16, 8).Formula = "=H15-F16"
ws.Cells(17, 8).Formula = "=H16-F17"
ws.Cells(18, 8).Formula = "=H17-F18"
ws.Cells(19, 8).Formula = "=H18-F19"
Call AutomateDepreciationExpense
Call AutomateDepreciationExpense1
Range("B2").Select
Unload UserForm1
End Sub
Sub AutomateDepreciationExpense()
Dim x As Integer
For x = 15 To 19
If Cells(x, 7).Value > Cells(9, 4).Value Then
Cells(x, 6).Formula = "=" & Cells(9, 4).Address(0, 0) & "-" & Cells(x - 1, 7).Address(0, 0)
Range(Cells(x + 1, 3), Cells(20, 8)).ClearContents
Else
End If
Next
End Sub
Sub AutomateDepreciationExpense1()
Dim x As Integer
For x = 14 To 15
If Cells(x, 4).Value > Cells(7, 4).Value Then
Cells(x, 6).Formula = "=D9"
Else
End If
Next
End Sub
Bookmarks