code to work on conditional match

[ndendrinos]

The attached sample needs to work conditionally on the values entered in range D1735, 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
    Sheets("Stock").Activate
    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)
    Next
    Call stock
 Sheets("Invoice").Activate
 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"
     UserForm9.Show
        

End Sub
 

[mdmackillop]

No idea what should be happening.
Please use Option Explicit in your coding and some comments would also be helpful.

[ndendrinos]

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