Help Please

[bhaktany]

I am trying to add code to my macro that will calculate interest on a mortgage. For example, in spreadsheet 1, i have 30 rows of data (each a different loan) that i need to calculate interest. I need the macro to calculate the interest for each of the 30 rows of data. Once calculated, i will use the sum function to add up the interest for all the loans in spreadsheet 2. Can anyone help me out?

 

[Smitty]

Welcome to the Board!

Can you post your code?

And what formula are you using to calculate the interest? What's the time frame? What's the format of the sheet?

Smitty

 

[bhaktany]

I am a beginner so try not to laugh too much at my code below. Thanks for your help.


Dim perdiem As Integer 'interest per day'
Dim numdays As Integer 'number of days of interest'
Dim upb2 As Integer 'current balance used to calculate the interest'
Dim intd As Integer 'interest paid to date'
Dim pr_rate As Integer 'rate'
Dim settledate As Integer 'settle date'
Dim interestcalc As Integer 'interest'

For a = 2 To 6500
Worksheets("MLS").Select.Activate
upb2 = Worksheets("MLS").Range("AF2")
pr_rate = Worksheets("MLS").Range("AH2")
perdiem = Application.WorksheetFunction.Product(pr_rate, 0.00003)
intd = Worksheets("MLS").Range("AD2")
Worksheets("Funding Memo").Select.Activate
settledate = Worksheets("Funding Memo").Range("A3")
numdays = Application.WorksheetFunction.Days360(intd, settledate)
interestcalc = CLng(Application.WorksheetFunction.Product(perdiem, numdays, upb2))
Worksheets("MLS").Range("DI2").Select
Selection = interestcalc
Next a
End