need macro

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
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,
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Can't think why you would need a macro to di this, but ...

Code:
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

Board Regular
Joined
Sep 17, 2002
Messages
200
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Sep 17, 2002
Messages
200

ADVERTISEMENT

below is the example
Book3
ABCD
1DatepaymtrecvdBalancebalafterpayment
210/05/1998150212
311/05/1998150212
412/05/19983196150212
513/05/1998150212
614/05/1998150212
715/05/1998150212
816/05/19983196150212
917/05/1998150212
1018/05/1998150212
1119/05/1998150212
1220/05/1998150212
1321/05/19983196150212
1422/05/1998150212
1523/05/1998150212
1624/05/1998150212
Sheet1
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

In cell D2 enter:

=C2-sum(B$2:B2)

and copy down as far as needed.
 

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry I don't understand.

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

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top