Loop in Excel VBA

Question

I have a logic in place, but no idea as to how to execute/code it in Excel. The logic is below:

At office, I need to find out the value of many old articles based on their purchase value and age. I don't want to use VDB or other inbuilt functions.

In my spreadsheet:

A1 = "Table" (Name of the article)  
B1 = "01/01/2005" (Date of purchase in MM/DD/YYYY format)  
C1 = "1000" (Purchase value)  
D1 = "=DATEDIF(B1,TODAY(),"Y")" (Gives the age of article in years)  
E1 = "20" (Percentage depreciation for first year, which varies based on article)  
F1 = "=C1*10%" '(Depreciated value should not be less than 10% of purchase value)  

Now, in G1 I want to calculate the depreciation value of article "A1" with purchase value "C1" for "D1" number of years, @ flat "E1"% for first year and 10% for subsequent years.

H1 = "=C1-G1" (Value of the article after depreciation)  
I1 = "=IF(H1<=F1,F1,H1)"

Please help me with a macro or formula to loop and find out the value of G1.

Also, I want to apply this to "n" number of rows since there are "n" number of articles.

EDIT

@assylias Thanks for enlightening me about SO policy, putting me to work for myself to find the answer.

After some 30 min of googling followed by trial and error, I successfully wrote a macro to do what I wanted. Here it goes:

Sub DepVal()
'
' DepVal Macro
' Macro by Prashanth JC
'
' Keyboard Shortcut: Ctrl+d
'
fYear = 0
dVal = 0
tYear = ActiveCell.Offset(0, -3)
purVal = ActiveCell.Offset(0, -4)
depFirst = ActiveCell.Offset(0, -2)
depOther = 10

If tYear >= 1 Then
    Do
        If fYear = 0 Then
            dVal = purVal - (purVal * depFirst) / 100
        Else
            dVal = dVal - (dVal * depOther) / 100
        End If

        fYear = fYear + 1

    Loop Until fYear = tYear

    ActiveCell.Value = dVal

Else
    ActiveCell.Value = purVal

End If

End Sub

Finally, I formatted the G1 cell to a Number with Zero decimal place. Everything works perfect now!

Thanks again, SO and assylias!

Answer 1

You don't need a macro, it's simple math.

Assuming that your percentage (in E1) is stored as .2 (for 20%), not actually 20, this formula would work:

=MAX(F1,C1*(1-E1)-(C1*0.1*(D1-1)))