Hi Everybody,
with the help of Mr. Joels code, I did the following. It would be very
helpful if anybody solve my problem.
Actually I am trying to create the Loading Slip on Button click. If the Item
code found in a range and if the exact quantity not matching for that Item,
then it should give the result of nearest match quantity along with the
Invoice number. And if once the Item shifted to loading slip then the same
invoice should not repeat.
The data is picking from another worksheets which looks like:
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

The Loading Slip should generate on button click like:
Sl. No. / Item Code / Description /Current Stock /Qty. to be Loaded /
Invoice Numbers.
1 €œA€� Fins 300 100 1001 This Invoice is 100 quantity.
2 €œB€� Flange 1500 1500 1009,1011

Private Sub cmdOk_Click()
Dim FoundCell As Range
Dim SecondField As Long
Dim intS As Integer
Dim wKs As Worksheet
Dim res As Variant
Dim iRow As Long
Set wKs = Worksheets("LoadingSlip")
firstfield = txtItem.Text
SecondField = txtQty.Text
iRow = wKs.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'intS = 2
found = False
With Worksheets("Pending").Range("F:F")
Set FoundCell = .Find(firstfield, LookIn:=xlValues)
'Set c = .Find(FirstField, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
If FoundCell.Offset(0, 1).Value < SecondField Then
FirstAddress = FoundCell.Address
Do
Set FoundCell = .FindNext(FoundCell)
If FoundCell.Offset(0, 1).Value = SecondField Then
found = True
Exit Do
End If
Loop While Not FoundCell Is Nothing And FoundCell.Address < FirstAddress
Else
found = True
End If
End If
End With
If found = True Then
res = Application.Evaluate("=sumproduct(('Pending'!F2:F6 5500= """ _
& firstfield & """ )*('Pending'!G2:G65500))")
MsgBox ("Currant Stock for " & FoundCell & " = " & res)
With wKs
..Cells(iRow, 1).Value = iRow - 1
..Cells(iRow, 2).Value = FoundCell.Value
..Cells(iRow, 3).Value = FoundCell.Offset(0, -2).Value
..Cells(iRow, 4).Value = res
..Cells(iRow, 5).Value = FoundCell.Offset(0, 1).Value
..Cells(iRow, 6).Value = FoundCell.Offset(0, -5).Value
End With
Me.txtItem.Text = ""
Me.txtQty.Text = ""
Me.txtItem.SetFocus
' enter your code here
Else
MsgBox ("Item Not Found")
End If
'intS = intS + 1
End Sub
Thanks in advance.

--
Thanks,
Vikram P. Dhemare

You are trying to solve a PACKING problem that mathematicans have bee trying
to solve for centuries. Packing problems consist of trying to select the
most efficent method of selecting items from different size boxes and putting
them into a new set of boxes.

In your case you have the same item in stock in multiple locations and
quantities and trying come up with an algorithm to select which is the best
choice to make. It may be better to select two smaller items from stock than
the larger one which is closest to the quantity you need. Lets not solve
this problem here.

I recommend puttting a list box with all the quantites of an item up on the
screen and letting a person choose the best option of single or multiple
quantities from stock.

Example
This example creates a list box and fills it with integers from 1 to 10.

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
For x = 1 To 10
lb.ControlFormat.AddItem x
Next
End With


"Vikram Dhemare" wrote:

Hi Everybody,
with the help of Mr. Joels code, I did the following. It would be very
helpful if anybody solve my problem.
Actually I am trying to create the Loading Slip on Button click. If the Item
code found in a range and if the exact quantity not matching for that Item,
then it should give the result of nearest match quantity along with the
Invoice number. And if once the Item shifted to loading slip then the same
invoice should not repeat.
The data is picking from another worksheets which looks like:
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

The Loading Slip should generate on button click like:
Sl. No. / Item Code / Description /Current Stock /Qty. to be Loaded /
Invoice Numbers.
1 €œA€� Fins 300 100 1001 This Invoice is 100 quantity.
2 €œB€� Flange 1500 1500 1009,1011

Private Sub cmdOk_Click()
Dim FoundCell As Range
Dim SecondField As Long
Dim intS As Integer
Dim wKs As Worksheet
Dim res As Variant
Dim iRow As Long
Set wKs = Worksheets("LoadingSlip")
firstfield = txtItem.Text
SecondField = txtQty.Text
iRow = wKs.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'intS = 2
found = False
With Worksheets("Pending").Range("F:F")
Set FoundCell = .Find(firstfield, LookIn:=xlValues)
'Set c = .Find(FirstField, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
If FoundCell.Offset(0, 1).Value < SecondField Then
FirstAddress = FoundCell.Address
Do
Set FoundCell = .FindNext(FoundCell)
If FoundCell.Offset(0, 1).Value = SecondField Then
found = True
Exit Do
End If
Loop While Not FoundCell Is Nothing And FoundCell.Address < FirstAddress
Else
found = True
End If
End If
End With
If found = True Then
res = Application.Evaluate("=sumproduct(('Pending'!F2:F6 5500= """ _
& firstfield & """ )*('Pending'!G2:G65500))")
MsgBox ("Currant Stock for " & FoundCell & " = " & res)
With wKs
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = FoundCell.Value
.Cells(iRow, 3).Value = FoundCell.Offset(0, -2).Value
.Cells(iRow, 4).Value = res
.Cells(iRow, 5).Value = FoundCell.Offset(0, 1).Value
.Cells(iRow, 6).Value = FoundCell.Offset(0, -5).Value
End With
Me.txtItem.Text = ""
Me.txtQty.Text = ""
Me.txtItem.SetFocus
' enter your code here
Else
MsgBox ("Item Not Found")
End If
'intS = intS + 1
End Sub
Thanks in advance.

--
Thanks,
Vikram P. Dhemare

Resp. Sir,
Thanks a lot for your guidance. Actually I am not very much familier with
programming. With this communities, i got the little bit idea of programming.
I have tried with your code of List, but gives error that variable not
defined. Could you help me as I am very much needed the solution as I want to
reduce the cycle time of documentaion part as well as loading / unloading of
material.
Also, can we add the check boxes in column(1) for multiple items & let the
user have the option to select the item & quantity which is to be taken in
Loading Slip. If the user selects the check boxes & press the button & create
the loading slip.
Can it be possible ?
I am very much optimistic that you will solve my problem.
--
Thanks,
Vikram P. Dhemare


"Joel" wrote:

You are trying to solve a PACKING problem that mathematicans have bee trying
to solve for centuries. Packing problems consist of trying to select the
most efficent method of selecting items from different size boxes and putting
them into a new set of boxes.

In your case you have the same item in stock in multiple locations and
quantities and trying come up with an algorithm to select which is the best
choice to make. It may be better to select two smaller items from stock than
the larger one which is closest to the quantity you need. Lets not solve
this problem here.

I recommend puttting a list box with all the quantites of an item up on the
screen and letting a person choose the best option of single or multiple
quantities from stock.

Example
This example creates a list box and fills it with integers from 1 to 10.

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
For x = 1 To 10
lb.ControlFormat.AddItem x
Next
End With


"Vikram Dhemare" wrote:

Hi Everybody,
with the help of Mr. Joels code, I did the following. It would be very
helpful if anybody solve my problem.
Actually I am trying to create the Loading Slip on Button click. If the Item
code found in a range and if the exact quantity not matching for that Item,
then it should give the result of nearest match quantity along with the
Invoice number. And if once the Item shifted to loading slip then the same
invoice should not repeat.
The data is picking from another worksheets which looks like:
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

The Loading Slip should generate on button click like:
Sl. No. / Item Code / Description /Current Stock /Qty. to be Loaded /
Invoice Numbers.
1 €œA€� Fins 300 100 1001 This Invoice is 100 quantity.
2 €œB€� Flange 1500 1500 1009,1011

Private Sub cmdOk_Click()
Dim FoundCell As Range
Dim SecondField As Long
Dim intS As Integer
Dim wKs As Worksheet
Dim res As Variant
Dim iRow As Long
Set wKs = Worksheets("LoadingSlip")
firstfield = txtItem.Text
SecondField = txtQty.Text
iRow = wKs.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'intS = 2
found = False
With Worksheets("Pending").Range("F:F")
Set FoundCell = .Find(firstfield, LookIn:=xlValues)
'Set c = .Find(FirstField, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
If FoundCell.Offset(0, 1).Value < SecondField Then
FirstAddress = FoundCell.Address
Do
Set FoundCell = .FindNext(FoundCell)
If FoundCell.Offset(0, 1).Value = SecondField Then
found = True
Exit Do
End If
Loop While Not FoundCell Is Nothing And FoundCell.Address < FirstAddress
Else
found = True
End If
End If
End With
If found = True Then
res = Application.Evaluate("=sumproduct(('Pending'!F2:F6 5500= """ _
& firstfield & """ )*('Pending'!G2:G65500))")
MsgBox ("Currant Stock for " & FoundCell & " = " & res)
With wKs
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = FoundCell.Value
.Cells(iRow, 3).Value = FoundCell.Offset(0, -2).Value
.Cells(iRow, 4).Value = res
.Cells(iRow, 5).Value = FoundCell.Offset(0, 1).Value
.Cells(iRow, 6).Value = FoundCell.Offset(0, -5).Value
End With
Me.txtItem.Text = ""
Me.txtQty.Text = ""
Me.txtItem.SetFocus
' enter your code here
Else
MsgBox ("Item Not Found")
End If
'intS = intS + 1
End Sub
Thanks in advance.

--
Thanks,
Vikram P. Dhemare

I think it is best you send me the file.

My email is

"Vikram Dhemare" wrote:

Resp. Sir,
Thanks a lot for your guidance. Actually I am not very much familier with
programming. With this communities, i got the little bit idea of programming.
I have tried with your code of List, but gives error that variable not
defined. Could you help me as I am very much needed the solution as I want to
reduce the cycle time of documentaion part as well as loading / unloading of
material.
Also, can we add the check boxes in column(1) for multiple items & let the
user have the option to select the item & quantity which is to be taken in
Loading Slip. If the user selects the check boxes & press the button & create
the loading slip.
Can it be possible ?
I am very much optimistic that you will solve my problem.
--
Thanks,
Vikram P. Dhemare


"Joel" wrote:

You are trying to solve a PACKING problem that mathematicans have bee trying
to solve for centuries. Packing problems consist of trying to select the
most efficent method of selecting items from different size boxes and putting
them into a new set of boxes.

In your case you have the same item in stock in multiple locations and
quantities and trying come up with an algorithm to select which is the best
choice to make. It may be better to select two smaller items from stock than
the larger one which is closest to the quantity you need. Lets not solve
this problem here.

I recommend puttting a list box with all the quantites of an item up on the
screen and letting a person choose the best option of single or multiple
quantities from stock.

Example
This example creates a list box and fills it with integers from 1 to 10.

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
For x = 1 To 10
lb.ControlFormat.AddItem x
Next
End With


"Vikram Dhemare" wrote:

Hi Everybody,
with the help of Mr. Joels code, I did the following. It would be very
helpful if anybody solve my problem.
Actually I am trying to create the Loading Slip on Button click. If the Item
code found in a range and if the exact quantity not matching for that Item,
then it should give the result of nearest match quantity along with the
Invoice number. And if once the Item shifted to loading slip then the same
invoice should not repeat.
The data is picking from another worksheets which looks like:
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

The Loading Slip should generate on button click like:
Sl. No. / Item Code / Description /Current Stock /Qty. to be Loaded /
Invoice Numbers.
1 €œA€� Fins 300 100 1001 This Invoice is 100 quantity.
2 €œB€� Flange 1500 1500 1009,1011

Private Sub cmdOk_Click()
Dim FoundCell As Range
Dim SecondField As Long
Dim intS As Integer
Dim wKs As Worksheet
Dim res As Variant
Dim iRow As Long
Set wKs = Worksheets("LoadingSlip")
firstfield = txtItem.Text
SecondField = txtQty.Text
iRow = wKs.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'intS = 2
found = False
With Worksheets("Pending").Range("F:F")
Set FoundCell = .Find(firstfield, LookIn:=xlValues)
'Set c = .Find(FirstField, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
If FoundCell.Offset(0, 1).Value < SecondField Then
FirstAddress = FoundCell.Address
Do
Set FoundCell = .FindNext(FoundCell)
If FoundCell.Offset(0, 1).Value = SecondField Then
found = True
Exit Do
End If
Loop While Not FoundCell Is Nothing And FoundCell.Address < FirstAddress
Else
found = True
End If
End If
End With
If found = True Then
res = Application.Evaluate("=sumproduct(('Pending'!F2:F6 5500= """ _
& firstfield & """ )*('Pending'!G2:G65500))")
MsgBox ("Currant Stock for " & FoundCell & " = " & res)
With wKs
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = FoundCell.Value
.Cells(iRow, 3).Value = FoundCell.Offset(0, -2).Value
.Cells(iRow, 4).Value = res
.Cells(iRow, 5).Value = FoundCell.Offset(0, 1).Value
.Cells(iRow, 6).Value = FoundCell.Offset(0, -5).Value
End With
Me.txtItem.Text = ""
Me.txtQty.Text = ""
Me.txtItem.SetFocus
' enter your code here
Else
MsgBox ("Item Not Found")
End If
'intS = intS + 1
End Sub
Thanks in advance.

--
Thanks,
Vikram P. Dhemare