# need macro

#### ehsas

##### Board Regular
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,

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
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``````

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,

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?

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

In cell D2 enter:

=C2-sum(B\$2:B2)

and copy down as far as needed.

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

Sorry I don't understand.

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

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,

Replies
5
Views
343
Replies
0
Views
453
Replies
2
Views
256
Replies
0
Views
615
Replies
0
Views
390

1,219,006
Messages
6,145,712
Members
450,635
Latest member
Rookie3510

### 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.

### Which adblocker are you using?

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

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