Vishal2204
New Member
- Joined
- Nov 12, 2014
- Messages
- 25
Hello,
I have a Excel File in which I have given some Excel functions to calculate the figures/amount. But I wanted to do all these this in a Form. I have created a form but I'm illiterate about <acronym title="visual basic for applications">VBA</acronym> Coding. can someone please help me.
1. Form should find empty row and add new bill.
2. Form Automatically get next Bill No. (if 2 bills are done it should take Bill No. 3)
3. One bill has multiple Products and Bill Amount should sum for all products for the same bill (I used SUMIF in Excel)
4. It should search bill, edit bill, add, or print bill.
5. Auto calculate totals.
Data:
[TABLE="width: 1075"]
<colgroup><col><col><col><col><col span="11"></colgroup><tbody>[TR]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Bill No.[/TD]
[TD="align: center"]Party Name[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Delivery Qty[/TD]
[TD="align: center"]Return Qty[/TD]
[TD="align: center"]Net Delivery[/TD]
[TD="align: center"]MRP Rate[/TD]
[TD="align: center"]Retailer Rate[/TD]
[TD="align: center"]MRP Total[/TD]
[TD="align: center"]Retailer Total[/TD]
[TD="align: center"]25% Less[/TD]
[TD="align: center"]Bill Amount[/TD]
[TD="align: center"]Received Amount[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]01-Apr-14[/TD]
[TD="align: center"]204[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"]Note Book[/TD]
[TD="align: center"]256[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]166[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]1660.00[/TD]
[TD="align: center"]1328.00[/TD]
[TD="align: center"]1245.00[/TD]
[TD="align: center"]2260.00[/TD]
[TD="align: center"]2200.00[/TD]
[TD="align: center"]60.00[/TD]
[/TR]
[TR]
[TD="align: center"]01-Apr-14[/TD]
[TD="align: center"]204[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"]Ink[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]36
[/TD]
[TD="align: center"]15.00[/TD]
[TD="align: center"]12.00[/TD]
[TD="align: center"]540.00[/TD]
[TD="align: center"]432.00[/TD]
[TD="align: center"]405.00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]01-Apr-14[/TD]
[TD="align: center"]204[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"]Pen[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]20.00[/TD]
[TD="align: center"]16.00[/TD]
[TD="align: center"]60.00[/TD]
[TD="align: center"]48.00[/TD]
[TD="align: center"]45.00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]02-Apr-14[/TD]
[TD="align: center"]205[/TD]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]Note Book[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]140[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]1400.00[/TD]
[TD="align: center"]1120.00[/TD]
[TD="align: center"]1050.00[/TD]
[TD="align: center"]1764.00[/TD]
[TD="align: center"]1700.00[/TD]
[TD="align: center"]64.00[/TD]
[/TR]
[TR]
[TD="align: center"]02-Apr-14[/TD]
[TD="align: center"]205[/TD]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]Ink[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]12.00[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]744.00[/TD]
[TD="align: center"]620.00[/TD]
[TD="align: center"]558.00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]02-Apr-14[/TD]
[TD="align: center"]205[/TD]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]Pen[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]30.00[/TD]
[TD="align: center"]24.00[/TD]
[TD="align: center"]22.50[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]
Please Help
I have a Excel File in which I have given some Excel functions to calculate the figures/amount. But I wanted to do all these this in a Form. I have created a form but I'm illiterate about <acronym title="visual basic for applications">VBA</acronym> Coding. can someone please help me.
1. Form should find empty row and add new bill.
2. Form Automatically get next Bill No. (if 2 bills are done it should take Bill No. 3)
3. One bill has multiple Products and Bill Amount should sum for all products for the same bill (I used SUMIF in Excel)
4. It should search bill, edit bill, add, or print bill.
5. Auto calculate totals.
Data:
[TABLE="width: 1075"]
<colgroup><col><col><col><col><col span="11"></colgroup><tbody>[TR]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Bill No.[/TD]
[TD="align: center"]Party Name[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Delivery Qty[/TD]
[TD="align: center"]Return Qty[/TD]
[TD="align: center"]Net Delivery[/TD]
[TD="align: center"]MRP Rate[/TD]
[TD="align: center"]Retailer Rate[/TD]
[TD="align: center"]MRP Total[/TD]
[TD="align: center"]Retailer Total[/TD]
[TD="align: center"]25% Less[/TD]
[TD="align: center"]Bill Amount[/TD]
[TD="align: center"]Received Amount[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]01-Apr-14[/TD]
[TD="align: center"]204[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"]Note Book[/TD]
[TD="align: center"]256[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]166[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]1660.00[/TD]
[TD="align: center"]1328.00[/TD]
[TD="align: center"]1245.00[/TD]
[TD="align: center"]2260.00[/TD]
[TD="align: center"]2200.00[/TD]
[TD="align: center"]60.00[/TD]
[/TR]
[TR]
[TD="align: center"]01-Apr-14[/TD]
[TD="align: center"]204[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"]Ink[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]36
[/TD]
[TD="align: center"]15.00[/TD]
[TD="align: center"]12.00[/TD]
[TD="align: center"]540.00[/TD]
[TD="align: center"]432.00[/TD]
[TD="align: center"]405.00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]01-Apr-14[/TD]
[TD="align: center"]204[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"]Pen[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]20.00[/TD]
[TD="align: center"]16.00[/TD]
[TD="align: center"]60.00[/TD]
[TD="align: center"]48.00[/TD]
[TD="align: center"]45.00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]02-Apr-14[/TD]
[TD="align: center"]205[/TD]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]Note Book[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]140[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]1400.00[/TD]
[TD="align: center"]1120.00[/TD]
[TD="align: center"]1050.00[/TD]
[TD="align: center"]1764.00[/TD]
[TD="align: center"]1700.00[/TD]
[TD="align: center"]64.00[/TD]
[/TR]
[TR]
[TD="align: center"]02-Apr-14[/TD]
[TD="align: center"]205[/TD]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]Ink[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]12.00[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]744.00[/TD]
[TD="align: center"]620.00[/TD]
[TD="align: center"]558.00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]02-Apr-14[/TD]
[TD="align: center"]205[/TD]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]Pen[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]30.00[/TD]
[TD="align: center"]24.00[/TD]
[TD="align: center"]22.50[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]
Please Help