Activity Based Depreciation If /Then VBA Code

I am attaching a workbook that contains activity based depreciation formulas.
I have written notes in it to explain the dilemma. I need to fill in a set of new values based on activity hours....
I was thinking that the user could input estimated activity hours with a userform and then based on that would fill in the chart. Once the userform is submitted and the chart is completed I need to run a set of if/then statements behind the values to evaluate if they are greater then residual value. IF they are I need to plug in a value... Does anyone have any ideas on how to simplify this process?

Any help and or suggestions/ideas would be much appreciated!

-Matt

I know that this post was not written well enough. I have reevaluated the process that needs to take place and believe that I have figured out a logical way to go about this process. I have posted a more detailed and well thought out notion of this process at: Link to Forum

Thank you for all who attempted this process. I am still working on it and if a solution arises I will post it here for your reference!!

I ended up making a user form for the depreciation schedule. This is the code I used to complete the automation:

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

---

Thanks mrmmickle for sharing this.

For the Sub EnterData_Click, you could write it this way

Code:

---

Private Sub EnterData_Click()   
    Dim ws As Worksheet
    Set ws = Worksheets(2)


  'Insert Year Headers
  ws.Cells(14, 3).Resize(6).Formula = "=IF(D14="""", """", ""Year ""&ROW()-13)"
 
  'Insert Hourly Rate
  ws.Cells(14, 5).Resize(6).Formula = "=$D$10"
 
  'Insert Depreciation Rate
  ws.Cells(14, 6).Resize(6).Formula = "=D14*E14"
 
  'Insert Accumulated Depreciation
  ws.Cells(14, 7).Resize(2).Formula = "=$F$14"
  ws.Cells(16, 7).Resize(4).Formula = "=G15+F16"
 
  'New Value
  ws.Cells(14, 8).Resize(2).Formula = "=$D$5-F14"
  ws.Cells(16, 8).Resize(4).Formula = "=H15-F16"
 
End Sub

---

ExcelFox,

Thank you for the tip. I see how much easier this is and less typing!! I appreciate you following up on my post!