creating a macro to calculate interest

[bhaktany]

I am trying to creat a macro that performs interest calculations by pulling data from various fields in spreadsheet "MLS". For all rows, varies by each deal, in MLS, I will add the total interest and take that amount and place it in spreadsheet "Funding Memo". I am a beginner so i am asking for your help. Thanks again for your suggestions.

'
'accrued interest calculation
'
Worksheets("MLS").Activate
Worksheets("MLS").Range("DI1:DI1").Select
Worksheets("MLS").Range("DI1:DI1").Activate
ActiveCell.Value = "Accrued Interest Calculation"
'
'
'
Dim perdiem As String 'interest per day'
Dim numdays As String 'number of days of interest'
Dim upb2 As String 'current balance used to calculate the interest'
Dim intd As Date 'interest paid to date'
Dim pr_rate As String 'rate'
Dim settledate As Date
Dim interestcalc As String 'interest'

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

End Sub

 

[tactps]

Quite a macro for a beginner.

Have you looked at the financial functions available in Excel to see if there is a formula you can use?