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 (http://www.mrexcel.com/forum/excel-questions/682892-offsetting-issues-if-then-statement-evaluate-first-occurrence-then-perform-action.html)

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!