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