if a=true and b=5 then....

[sryall]

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

 

[John]

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

 

[sryall]

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

 

[John]

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

 

[John]

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