Editing Formulas

Hi all

Here is a tricky question;

I'm doing some mortgage calculations using Excel's PMT formula, currently what I've been doing is this;

I have an input sheet with my raw data - I then process the raw data in another sheet, where I'm doing numerous calculations. One of those is the PMT. So far I've been using a fixed loan period, based on estimates, but I would like to use the real periods instead - Is it possible to insert a reference based on a for next in a formula, like the PMT?

/Nick

So...'

I managed to go around the problem of inserting variables into the PMT formula, but instead I've - of course - encountered another problem.

Here is what I'm doing:

VB Code:

Sub flexvalue()
Dim j As Double
Dim k As Double
Dim iSheet As Worksheet
Dim nsheet As Worksheet
 
    Set iSheet = ActiveWorkbook.Sheets("Input")
    Set nsheet = ActiveWorkbook.Sheets("Nedskrivning-7")
    
    For j = 1 To 10
        For k = 1 To 25
            If nsheet.Cells(k, "A").Value = iSheet.Cells(j, "O").Value Then
                nsheet.Range(Cells(k + 1, "A"), Cells(k + 1, "O").End(xlDown)).Select
                nsheet.Range("P17").Value = iSheet.Cells(j, "O").Value
                nsheet.Range("P18").Value = nsheet.Range("P17").Value - 1
                nsheet.Range("P19").Value = nsheet.Range("P18").Value - 1
                nsheet.Range("P20").Value = nsheet.Range("P19").Value - 1
                nsheet.Range("P21").Value = nsheet.Range("P20").Value - 1
            End If
        Next k
    Next j
        
    Set iSheet = Nothing
    Set nsheet = Nothing
    
End Sub

However I can't use this part;

VB Code:

nsheet.Range("P17").Value = iSheet.Cells(j, "O").Value
                nsheet.Range("P18").Value = nsheet.Range("P17").Value - 1
                nsheet.Range("P19").Value = nsheet.Range("P18").Value - 1
                nsheet.Range("P20").Value = nsheet.Range("P19").Value - 1
                nsheet.Range("P21").Value = nsheet.Range("P20").Value - 1

First off, becuase there must be an easier way - and more importantly, I will need to "control" somehow, so if the number is larger than 10, it should state 10 for the largest AND the number can't be lower than 1.

So if the number is 35, it will write 10 instead, the next number will then be 9...8...7...until it reaches 1...after that nothing!

Anyone out there who has a good idea?

Nick