Howdy... new to VBA programming and I have a project in front o
me.....

I am working on a spreadsheet to caculate payment for our speedskatin
association where most of the data is automated.

I would like to be able to extend my nested if's to also include
second if

eg. if date=june 6/04 & if no of payments=5 then calculate the payment
this way... but if date=june 6/04 & no. of pay=1 then have all th
money due on this date... or in 3 payments or 2 payments... and we hav
it totalled to cross reference it for our VP of fiance (how much shoul
be deposited on Oct 1 and number of items to be deposited) [counta i
great]

in brief - people can take up to 5 months to pay the fees - dependin
on the date they register - July 1-Nov 1. Some people prefer to pay al
up front though so the no. of payments =1 but we don't want the defaul
payment to be on Nov 1 but rather on the date of the registation or th
1st of the next month.

I took pascal training MANY moons ago so I understand the If THEN ELS
idea (just can'tr remember the syntax)... how can I set up an IF and I
THEN ELSE nested option?

Clear as mud?

Thanks in advance :cool

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

Can I assume your sheet is set up like this?

John

Name Annual fee No of Payments 6/1 7/1 etc
tom 100 1 100
bob 100 2 50 50



-----Original Message-----
Howdy... new to VBA programming and I have a project in
front of
me.....

I am working on a spreadsheet to caculate payment for our
speedskating
association where most of the data is automated.

I would like to be able to extend my nested if's to also
include a
second if

eg. if date=june 6/04 & if no of payments=5 then
calculate the payments
this way... but if date=june 6/04 & no. of pay=1 then
have all the
money due on this date... or in 3 payments or 2
payments... and we have
it totalled to cross reference it for our VP of fiance
(how much should
be deposited on Oct 1 and number of items to be
deposited) [counta is
great]

in brief - people can take up to 5 months to pay the
fees - depending
on the date they register - July 1-Nov 1. Some people
prefer to pay all
up front though so the no. of payments =1 but we don't
want the default
payment to be on Nov 1 but rather on the date of the
registation or the
1st of the next month.

I took pascal training MANY moons ago so I understand the
If THEN ELSE
idea (just can'tr remember the syntax)... how can I set
up an IF and IF
THEN ELSE nested option?

Clear as mud?

Thanks in advance


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

.

Yes - the amounts are calculated from serveral other cells so we can ge
a total per family. Then a total then # of payments then the amount pe
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
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 nex
level.

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

Thank

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

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/

.

I added two more features....John

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
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 monthtotal(50) 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) 0 Then GoTo notest
If Noofpayments(i) + j totalmonths + 5 Then GoTo addmonth
notest:
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
'Add Monthly Totals
Cells(lastrow + 2, 1) = "Totals"
For j = 5 To totalmonths + 4
monthtotal(j) = 0
For i = firstrow + 1 To lastrow
monthtotal(j) = monthtotal(j) + Cells(i, j).Value
Next i
Cells(lastrow + 2, j).Value = monthtotal(j)
Cells(lastrow + 2, j).NumberFormat = "#,##0.00_);[Red]
(#,##0.00)"
Cells(lastrow + 2, j).HorizontalAlignment = xlCenter
Next j
GoTo theend
addmonth:
MsgBox ("You need to add another month!")
theend:
End Sub