Offsetting Issues/If Then Statement to evaluate First Occurrence Then Perform Action

[mrmmickle1]

I am unsure how to set a cell as a starting point from offset based on criteria. The project that I am working on that I have run into a dilemma. I am currently working on a activity based depreciation model. I have basically created a userform that inserts formulas into cells in the area $D$14:$H$18. So when the user submits the form it inserts all of the correct formulas into this area. However in the area G14 to G18 I need to look and find the first occurrence of a cell if the value is > than the value in cell $d$10. From this cell (Lets call this cell first occurrence) I need to offset to the cell to first occurrences left and enter a formula that =$D$6 - (the location of the cell above the first occurence cell). So for example if G16 was the first occurence > then $d$10 then the formula in the cell to its left would need to be =$d$6 -g15. After this formula was completed I would need to clear contents of the rows directly below the first occurence cell. For example if G16 was the first occurence I would want to clear $c$17:$h$18. IF the first occurence was is G14 i would want to clear $c$17:$h$18. Here is what my data looks like:

Excel 2010
	C	D	E	F	G	H
6	Machine Cost	$80,000
7	Total Depreciation Life (In Years)	5
8	Total Machine Life (In Hours)	10400
9	Salvage Value	$10,000
10	Residual Value	$70,000
11	Depreciation Rate Per Hour	$7.69
12
13	Depreciation	Hour Usage	Depreciation Rate	Depreciation Expense	Accumulated Depreciation	New Value
14	Year 1	5000	$7.69	$38,461.54	$38,461.54	$41,538.46
15	Year 2	6000	$7.69	$46,153.85	$84,615.38	-$4,615.38
16	Year 3	7000	$7.69	$53,846.15	$138,461.54	-$58,461.54
17	Year 4	8000	$7.69	$61,538.46	$200,000.00	-$120,000.00
18	Year 5	9000	$7.69	$69,230.77	$269,230.77	-$189,230.77
19
20	Totals	26000		$138,461.54
Depreciation

I know this is alot to ask but, I thought you all may be able to help me solve the problem I am having.

Here is the code I have for the userform as well if it helps any:

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 Hourly Rates
   ws.Cells(14, 4).Value = TextBox1.Value
   ws.Cells(15, 4).Value = TextBox2.Value
   ws.Cells(16, 4).Value = TextBox3.Value
   ws.Cells(17, 4).Value = TextBox4.Value
   ws.Cells(18, 4).Value = TextBox5.Value
   'Insert Hourly Rate
   ws.Cells(14, 5).Formula = "=$D$11"
   ws.Cells(15, 5).Formula = "=$D$11"
   ws.Cells(16, 5).Formula = "=$D$11"
   ws.Cells(17, 5).Formula = "=$D$11"
   ws.Cells(18, 5).Formula = "=$D$11"
   'Insert Depreciation Rate
   ws.Cells(14, 6).Formula = "=IFERROR(D14*E14, ""N/A"")"
   ws.Cells(15, 6).Formula = "=IFERROR(D15*E15, ""N/A"")"
   ws.Cells(16, 6).Formula = "=IFERROR(D16*E16, ""N/A"")"
   ws.Cells(17, 6).Formula = "=IFERROR(D17*E17, ""N/A"")"
   ws.Cells(18, 6).Formula = "=IFERROR(D18*E18, ""N/A"")"
   'Insert Accumulated Depreciation
   ws.Cells(14, 7).Formula = "=F14"
   ws.Cells(15, 7).Formula = "=G14+F15"
   ws.Cells(16, 7).Formula = "=G15+F16"
   ws.Cells(17, 7).Formula = "=G16+F17"
   ws.Cells(18, 7).Formula = "=G17+F18"
   'New Value
   ws.Cells(14, 8).Formula = "=$D$6-F14"
   ws.Cells(15, 8).Formula = "=H14-F15"
   ws.Cells(16, 8).Formula = "=H15-F16"
   ws.Cells(17, 8).Formula = "=H16-F17"
   ws.Cells(18, 8).Formula = "=H17-F18"
   
   Unload UserForm1
End Sub

The reason I would like to do this process is because in activity based depreciation the New Value cannot be less than the salvage value. So if it is then you have to back out the depreciation and make it lower...

Thanks so much,

-Matt

 

[mrmmickle1]

In the above example the resulting action would look like this:

Excel 2010
	C	D	E	F	G	H
13	Depreciation	Hour Usage	Depreciation Rate	Depreciation Expense	Accumulated Depreciation	New Value
14	Year 1	5000	$7.69	$38,461.54	$38,461.54	$41,538.46
15	Year 2	6000	$7.69	$31,538.46	$70,000.00	$10,000.00
16
17
18
19
20	Totals	11000		$70,000.00
Depreciation

 

[mrmmickle1]

I am still working on this process. I was hoping someone could help me reference the cell g15 in relative terms in this code. I am trying to go down the values in g14:g18 and find the value that is > the value in d10. In the above case this occurs in cell g15. how do i set a reference point from the first occurence of the value > d10? i would like to use it in the below code in order to help correct the depreciation table.... I have a start but, I know this code has errors. I am unsure how to do this process.

Sub mrmmickle1()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("G14:G18" & LastRow)
   If Cell.Value > Cell.Value("D10") Then 'If the value of the cell in column A is equal to the one below it then

    Cell.Offset(0, -1).Value.Formula = "=D10 - (value of the cell above g15)"

Any help would be great!

 

[mrmmickle1]

Bump. This can't be too incredibly hard. I just know that I don't know how to do it....

 

[mrmmickle1]

I think I am getting closer. any suggestions?

Sub mrmmickle1()
LastRow = Range("C" & Rows.Count).End(xlUp).Row
For Each Cell In Range("G14:G18" & LastRow)
   If Cell.Value > Cell.Value("D10") 
    Cell.Offset(0, -1).Value.Formula =
    ActiveCell.FormulaR1C1 = "=D10-R[-1]C[1]
    End If
    Next
  End Sub