Activity Based Depreciation If /Then VBA Code

[mrmmickle1]

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

[mrmmickle1]

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!!

[mrmmickle1]

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

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

[Excel Fox]

Thanks mrmmickle for sharing this.

For the Sub EnterData_Click, you could write it this way

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

[mrmmickle1]

ExcelFox,

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