ndendrinos
03-14-2010, 10:13 AM
Hello,
The first code reduces my stock according to the quantities shipped
Sub tostock()
Application.ScreenUpdating = False
Dim TgtRw As Long
Dim Dt
Dim Inv
Dim Prodlist As Range
Dim c As Long
With Sheets("Invoice")
Dt = .Range("F3").Value
Inv = .Range("F2").Value
End With
Sheets("Stock").Activate
With Sheets("Stock")
TgtRw = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set Prodlist = .Range("A4:N4")
End With
Cells(TgtRw, 1) = Dt
Cells(TgtRw, 2) = Inv
For Each cel In Range("Products")
c = Application.Match(cel, Prodlist, 0)
Cells(TgtRw, c) = -cel.Offset(, -1)
Next
Call chkstock
Sheets("Invoice").Activate
Application.ScreenUpdating = True
End Sub
This code is a warning that the stock of one or many items in stock is low (less than 50).
Sub chkstock()
Dim c As Range, msg As String
'the range here is where the quantity in stock is . Row2 starts at "C"
For Each c In Range("C2:N2")
If c.Value < 50 Then
msg = msg & c.Offset(2).Value & vbCr
End If
Next c
MsgBox "Stock is low ! Reorder soon :" & Chr$(13) & Chr$(13) & vbCr & msg
End Sub
There is something wrong with the second code as it gives warning messages when there should not be any.
Thank you
The first code reduces my stock according to the quantities shipped
Sub tostock()
Application.ScreenUpdating = False
Dim TgtRw As Long
Dim Dt
Dim Inv
Dim Prodlist As Range
Dim c As Long
With Sheets("Invoice")
Dt = .Range("F3").Value
Inv = .Range("F2").Value
End With
Sheets("Stock").Activate
With Sheets("Stock")
TgtRw = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set Prodlist = .Range("A4:N4")
End With
Cells(TgtRw, 1) = Dt
Cells(TgtRw, 2) = Inv
For Each cel In Range("Products")
c = Application.Match(cel, Prodlist, 0)
Cells(TgtRw, c) = -cel.Offset(, -1)
Next
Call chkstock
Sheets("Invoice").Activate
Application.ScreenUpdating = True
End Sub
This code is a warning that the stock of one or many items in stock is low (less than 50).
Sub chkstock()
Dim c As Range, msg As String
'the range here is where the quantity in stock is . Row2 starts at "C"
For Each c In Range("C2:N2")
If c.Value < 50 Then
msg = msg & c.Offset(2).Value & vbCr
End If
Next c
MsgBox "Stock is low ! Reorder soon :" & Chr$(13) & Chr$(13) & vbCr & msg
End Sub
There is something wrong with the second code as it gives warning messages when there should not be any.
Thank you