I think it is best you send me the file.
My email is
(E-Mail Removed)
"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. Joel’s 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:F65500= """ _
> > > & 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
Similar Threads
