View Full Version : code to work on conditional match

03-01-2011, 11:12 AM
The attached sample needs to work conditionally on the values entered in range D17:D35, Presently it does not.
I post the codes here but the attachment will illustrate the problem better.
Many thanks

Sub deductfromstock()
Application.ScreenUpdating = False
Dim TgtRw As Long
Dim Dt
Dim Inv
Dim Inventorylist As Range
Dim c As Long

With Sheets("Invoice")
Dt = .Range("H4").Value
Inv = .Range("H2").Value
End With
With Sheets("Stock")
TgtRw = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set Inventorylist = .Range("inventory")
End With
Cells(TgtRw, 1) = Dt
Cells(TgtRw, 2) = Inv
For Each Cel In Range("Products")
c = Application.Match(Cel, Inventorylist, 0)
Cells(TgtRw, c) = -Cel.Offset(, -2)
Call stock
Application.ScreenUpdating = True

End Sub
Sub stock()
Dim c As Range, msg As String
For Each c In Range("lefttominimum")
'If c.Value > .Range("lefttominimum") Then '(here minimum means zero)
If c.Value < 1 Then
msg = msg & c.Offset(1).Value & vbCr
End If
Next c
MsgBox "Stock is low ... reorder soon" & vbCr & msg
Call nostock

End Sub
Sub nostock()
For Each c In Range("nomorestock")
If c.Value < 0 Then
msg = msg & c.Offset(3) & c.Offset(0).Value & vbCr
End If
Next c
MsgBox "not enough stock to fill" & Chr$(13) & "Missing qty is indicated next to item" & vbCr & msg
Call YesNoMessageBox

End Sub
Sub YesNoMessageBox()
Dim MyNote As String

'Body of message box
MyNote = "There is not enough stock to fill this order" & Chr$(13) & " Click Yes to ship what's available" & Chr$(13) & "Click No to delete this/these item(s) from the Invoice"

End Sub

03-01-2011, 12:16 PM
No idea what should be happening.
Please use Option Explicit in your coding and some comments would also be helpful.

03-01-2011, 02:39 PM
Must have been you missed my attachment so maybe I can explain here.
Every time the code on "Invoice" is run the quantities ordered get deducted for sheet "Stock"

the code is for an simple inventory control with an "alarm" to the user.

In "Stock":
1st row indicates qty on hand.

Row two is for the minimum required stock.This is a decision the user will take and enter the quantity needed.

Row three turn negative when the minimum required stock's threshold is broken the first msgbox appears as a reminder and lists the "products" that need reodering.

Row four lists the products by name.

As long as there is stock over the "desired stock" as entered in row two the code will not trigger.

As long as the quantity requested on "Invoice" finds enough stock in "Stock" the order proceeds without any msgboxes poping up (I call tehse alarm to the user)

When the stock is reduced between the desired stock level and zero the first msgbox will show.

When the processing of an invoice cannot find enough stock to fill the order then the first msg will appear followed by a second that lists the products that cannot be filled with the quantities requested on the invoice shows the deficit (i.e. Telephone-3)

On closing the second msg a userform shows and the user has two choice:
a) to delete the product from the invoice
b) reduce the quantity requested to what's left in stock by choosing the "short" product from the lisbox on the UserForm ans clicking the button on the form.
If the quantity is reduced then the column "B/O" is populated and thru more code a back order sheet is created and sits there until new stock is ordered & received and at that time the back order can be filled.

All the above codes work but there is a problem:

On the Userform the user will click the item that is "short" from the listbox to the left or the right then will decide to decrease the qty ordered to what's available in stock.If the user chooses (by mistake) a product that is not on the invoice he is prompted and can re-chose the right one.

Unfortunately the way the code is written the userform shows when ANY product is below zero on the first row of "Stock" whether that same product is or is not requested on the invoice.(It should only appear in the product is listed on the Invoice)

The attachment is well documented and if you can spare a minute and run it all of the above will become I hope even clearer as I am sorry to say sometimes have difficulty in articulating my thoughts in English.

Thank you