V
Vikram Dhemare
Hello Everybody,
I have a excel file, the ledger of Inward / Outward Material with various
User Forms where users fill the data for Inward / Outward Matl. The file is
very useful to get the Current Stock Statement by using the
sumproduct.Evaluation function.
Now, I want to develop the macro for creating Packing List or Loading Slip
from the same file, which would be helpful to the Despatching Users. There
is one sheet which contents the data of stock lying in the warehouse, the
sheet is named “Pending Invoicesâ€. The User will press the button & the
system will prompt for user form for asking the field of Item Code & the
desired qty. Here I am stuck up. I need help to write code for the same.
The Pending Invoice Sheet contents the data as below fields.
Col. A Col B Col C Col D Col. E
Invoice Number / Inward Date / Item Code / Description /Qty Recd.
The macro code should find the textbox values (Item Code & required qty) in
the worksheet named “Pending Invoicesâ€, if found both the fields i.e. the
Item code & reqd. Qty. then create Packing Slip in new worksheet as below
fields.
Col. A Col B Col C Col D Col. E Col. F
Sl. No. / Item Code / Description /Current Stock /Qty. to be Loaded /
Invoice Numbers.
for Example,
Col. A Col B Col C Col D Col. E
Invoice Number / Inward Date / Item Code / Description /Qty Recd.
1001 28/08/2007 “A†Fins 100
1007 29/08/2007 “A†Fins 200
1009 28/08/2007 “B†Flange 500
1011 29/08/2007 “B†Flange 1000
If User asks for Item Code “A†& Reqd. Qty is “50†& Item Code “B†& Qty is
“1500†then it should generate the Packing Slip like:
Col. A Col B Col C Col D Col. E Col. F
Sl. No. / Item Code / Description /Current Stock /Qty. to be Loaded /
Invoice Numbers.
1 “A†Fins 300 100 1001
2 “B†Flange 1500 1500 1009,1011
That means two field must be match. If field reqd. Qty. does not match then
the msg box should pop up for nearest qty for that Item so that User can
decide wheather to create the loading slip or not.
Any help in this regard will be highly appreciated.
I have a excel file, the ledger of Inward / Outward Material with various
User Forms where users fill the data for Inward / Outward Matl. The file is
very useful to get the Current Stock Statement by using the
sumproduct.Evaluation function.
Now, I want to develop the macro for creating Packing List or Loading Slip
from the same file, which would be helpful to the Despatching Users. There
is one sheet which contents the data of stock lying in the warehouse, the
sheet is named “Pending Invoicesâ€. The User will press the button & the
system will prompt for user form for asking the field of Item Code & the
desired qty. Here I am stuck up. I need help to write code for the same.
The Pending Invoice Sheet contents the data as below fields.
Col. A Col B Col C Col D Col. E
Invoice Number / Inward Date / Item Code / Description /Qty Recd.
The macro code should find the textbox values (Item Code & required qty) in
the worksheet named “Pending Invoicesâ€, if found both the fields i.e. the
Item code & reqd. Qty. then create Packing Slip in new worksheet as below
fields.
Col. A Col B Col C Col D Col. E Col. F
Sl. No. / Item Code / Description /Current Stock /Qty. to be Loaded /
Invoice Numbers.
for Example,
Col. A Col B Col C Col D Col. E
Invoice Number / Inward Date / Item Code / Description /Qty Recd.
1001 28/08/2007 “A†Fins 100
1007 29/08/2007 “A†Fins 200
1009 28/08/2007 “B†Flange 500
1011 29/08/2007 “B†Flange 1000
If User asks for Item Code “A†& Reqd. Qty is “50†& Item Code “B†& Qty is
“1500†then it should generate the Packing Slip like:
Col. A Col B Col C Col D Col. E Col. F
Sl. No. / Item Code / Description /Current Stock /Qty. to be Loaded /
Invoice Numbers.
1 “A†Fins 300 100 1001
2 “B†Flange 1500 1500 1009,1011
That means two field must be match. If field reqd. Qty. does not match then
the msg box should pop up for nearest qty for that Item so that User can
decide wheather to create the loading slip or not.
Any help in this regard will be highly appreciated.