Code Reqd. to generate Loading Slip

[Guest]

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.

 

[Guest]

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

 

[Guest]

Thanks! Very Helpful.
Can it vlookup for Invoice Number in last column of loading slip with coma
separared?

 

[Guest]

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