Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Reqd. to generate Loading Slip
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. -- Thanks, Vikram P. Dhemare |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Reqd. to generate Loading Slip
I took the VBA help for the find function and added coded to search for two
fields. This should get you started. Sub test() FirstField = textbox1.Text SecondField = textbox2.Text found = False With Worksheets(1).Range("a1:a500") Set c = .Find(FirstField, LookIn:=xlValues) If Not c Is Nothing Then If c.Offset(0, 1).Value < SecondField Then firstAddress = c.Address Do Set c = .FindNext(c) If c.Offset(0, 1).Value = SecondField Then found = True Exit Do End If Loop While Not c Is Nothing And c.Address < firstAddress Else found = True End If End If End With If found = True Then ' enter your code here Else MsgBox ("Item Not Found") End If End Sub "Vikram Dhemare" wrote: 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. -- Thanks, Vikram P. Dhemare |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Reqd. to generate Loading Slip
Thanks! Very Helpful.
Can it vlookup for Invoice Number in last column of loading slip with coma separared? -- Thanks, Vikram P. Dhemare "Joel" wrote: I took the VBA help for the find function and added coded to search for two fields. This should get you started. Sub test() FirstField = textbox1.Text SecondField = textbox2.Text found = False With Worksheets(1).Range("a1:a500") Set c = .Find(FirstField, LookIn:=xlValues) If Not c Is Nothing Then If c.Offset(0, 1).Value < SecondField Then firstAddress = c.Address Do Set c = .FindNext(c) If c.Offset(0, 1).Value = SecondField Then found = True Exit Do End If Loop While Not c Is Nothing And c.Address < firstAddress Else found = True End If End If End With If found = True Then ' enter your code here Else MsgBox ("Item Not Found") End If End Sub "Vikram Dhemare" wrote: 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. -- Thanks, Vikram P. Dhemare |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Reqd. to generate Loading Slip
The find functon is equivalent to the Vlookup function but is more powerful.
Find() doesn't just return the text in the cell. Find() returns everything in the cell including formula, format,row number, column number, cell width, cell height, etc ... "Vikram Dhemare" wrote: Thanks! Very Helpful. Can it vlookup for Invoice Number in last column of loading slip with coma separared? -- Thanks, Vikram P. Dhemare "Joel" wrote: I took the VBA help for the find function and added coded to search for two fields. This should get you started. Sub test() FirstField = textbox1.Text SecondField = textbox2.Text found = False With Worksheets(1).Range("a1:a500") Set c = .Find(FirstField, LookIn:=xlValues) If Not c Is Nothing Then If c.Offset(0, 1).Value < SecondField Then firstAddress = c.Address Do Set c = .FindNext(c) If c.Offset(0, 1).Value = SecondField Then found = True Exit Do End If Loop While Not c Is Nothing And c.Address < firstAddress Else found = True End If End If End With If found = True Then ' enter your code here Else MsgBox ("Item Not Found") End If End Sub "Vikram Dhemare" wrote: 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. -- Thanks, Vikram P. Dhemare |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Codes Reqd. to create Loading Slip | Excel Discussion (Misc queries) | |||
How to generate code from VBA and... run it ! | Excel Programming | |||
loading vba code in excel from within C# | Excel Programming | |||
Disable VBA code execution when loading a workbook | Excel Discussion (Misc queries) | |||
Addins not loading when opening from code | Excel Programming |