need macro

[ehsas]

Need macro to do following:-

I have loan a/c which is outstanding amounts as 153408 and installment amount as 3196.The due date if 10 of each month starting 10/05/1998.I want to make a macro to change his outstanding balance at 10 of each month.for example 10/05 become 150212 and remain same till 9/6 on 10/6 it become 147016 and change accordingly on each month.

Also from 10/05 to 09/06 it sholuld fill the cell in column with same amount of 150212.and from 10/06 to 09/07 with 147016.Please if any one could help.Regards,

 

[Andrew Poulsom]

Can't think why you would need a macro to di this, but ...

Sub Test()
    Const Payment As Integer = 3196
    Dim NewSh As Worksheet
    Dim StartDate As Date
    Dim EndDate As Date
    Dim TheDate As Date
    Dim x As Integer
    Dim Balance As Long
    Set NewSh = Worksheets.Add
    StartDate = #5/10/1998#
    EndDate = #7/9/1998#
    TheDate = StartDate
    Balance = 150212
    x = 3
    With NewSh
        Cells(1, 1) = "Date"
        Cells(1, 2) = "Balance"
        Cells(2, 1) = TheDate
        Cells(2, 1).NumberFormat = "dd/mm/yyyy"
        Cells(2, 2) = Balance
        Do
            TheDate = TheDate + 1
            Cells(x, 1) = TheDate
            Cells(x, 1).NumberFormat = "dd/mm/yyyy"
            If Day(TheDate) = 10 Then Balance = Balance - Payment
            Cells(x, 2) = Balance
            x = x + 1
        Loop Until TheDate > EndDate
    End With
End Sub

 

[ehsas]

Thanks a million.

I need another favour.I need another macro which do the following:
Actually in column c I have received the payment received with some amounts.The outstanding balance at the biginning of loan is 150212.I want the macro the look in column c and if there is payment the outstanding balance should - payment received.for example,

In column d it starts with 150212 and fill the cells below that till if it found amount in column c and show the new outstanding in column d which is 150212-amount received in colum c and do the same till next payment received this excersice need to be done till today's date.
Start date can be 10 may 98.

Regards,

 

[Andrew Poulsom]

Wouldn't it be easier to do this with formulas if you already have the payments in column C?

What does your data in column C look like and where are the payment dates?

 

[ehsas]

below is the example

Book3
	A	B	C	D
1	Date	paymtrecvd	Balance	balafterpayment
2	10/05/1998		150212
3	11/05/1998		150212
4	12/05/1998	3196	150212
5	13/05/1998		150212
6	14/05/1998		150212
7	15/05/1998		150212
8	16/05/1998	3196	150212
9	17/05/1998		150212
10	18/05/1998		150212
11	19/05/1998		150212
12	20/05/1998		150212
13	21/05/1998	3196	150212
14	22/05/1998		150212
15	23/05/1998		150212
16	24/05/1998		150212
Sheet1

 

[ehsas]

I have added payment received in column B.Regards,

 

[Andrew Poulsom]

In cell D2 enter:

=C2-sum(B$2:B2)

and copy down as far as needed.

 

[ehsas]

Sorry the above formula is not what I want.It looking into column and then minus the amount from column b.Where I don't want to look for column c.The balance at start is 150212 and look directly into column c and minus outstanding if amount found.Regards

 

[Andrew Poulsom]

Sorry I don't understand.

What should the balances be on 12/05/1998 and 16/05/1998? And why?

 

[ehsas]

The o/s balance as 12/05/98 should be 147016 which is 152012 - b4 and n 16/05/98 the o/s balance should be 143820 which is 147016 - b8.
Regards,