Try this...John


Option Explicit
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/6/04 by John Faulstich
'
Sheets("payment_schedule").Select
Dim lastrow As Integer
Dim firstrow As Integer
Dim month(50) As Date
Dim startmonth(100) As Date
Dim Noofpayments(100) As Integer
Dim TotalPayment(100) As Double
Dim paymentsmade(100) As Double
Dim totalmonths As Integer
Dim i As Integer
Dim j As Integer
'
'Allow up to 300 rows of data in Column A
Cells(1, 1).Offset.End(xlDown).Select
firstrow = ActiveCell.Row
Cells(300 + firstrow, 1).Offset.End(xlUp).Select
lastrow = ActiveCell.Row
'
Cells(firstrow, 1).Offset.End(xlToRight).Select
totalmonths = ActiveCell.Column - 4
'
' Read in Data
' Sheet set up as follows:
' 1st Payment date is in column A, Name in column B, Total
Payment in Column C
' # of Payments in Column D, and the months of payments
due (June, July, etc)
' start in Column E
'
' 1st payment Dates
For i = firstrow + 1 To lastrow
startmonth(i) = Cells(i, 1).Value
Next i
'
' Total Payments
For i = firstrow + 1 To lastrow
TotalPayment(i) = Cells(i, 3).Value
Next i
' # of Payments
For i = firstrow + 1 To lastrow
Noofpayments(i) = Cells(i, 4).Value
Next i
' Payment Months
For i = 5 To totalmonths + 4
month(i) = Cells(firstrow, i).Value
Next i
For i = firstrow + 1 To lastrow
paymentsmade(i) = 0
For j = 5 To totalmonths + 4
If month(j) < startmonth(i) Then GoTo nextj
If paymentsmade(i) = TotalPayment(i) Then GoTo nexti
Cells(i, j).Value = TotalPayment(i) / Noofpayments(i)
'
' Format Output area
'
Cells(i, j).NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Cells(i, j).HorizontalAlignment = xlCenter
paymentsmade(i) = paymentsmade(i) + _
TotalPayment(i) / Noofpayments(i)
nextj:
Next j
nexti:
Next i
End Sub

-----Original Message-----
Yes - the amounts are calculated from serveral other
cells so we can get
a total per family. Then a total then # of payments then
the amount per
month

Smith $450 5 90 90 90 90 90

What I trying to add is that it the reg. date is Sept
that they have 3
max payments:

June 6 Smith $450 3 0 0 150 150 150

but that if they want to make 2 payments instead

Sept 1 Smith $450 2 0 0 225 225 0

my nested IFs are working well but I looking to take it
to the next
level.

Have been reading about VBA and procedures and functions
and ... just
not good enough yet...

Thanks


---
Message posted from http://www.ExcelForum.com/

.