PDA

View Full Version : Solved: uncalled for msg



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

hardlife
03-14-2010, 10:33 AM
Hi, try this



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

If Len(msg) > 1 Then
'MsgBox Len(msg)
MsgBox "Stock is low ! Reorder soon :" & Chr$(13) & Chr$(13) & vbCr & msg
Else
MsgBox "Stock is OK"
End If

End Sub



HTH, Pavel :hi:

ndendrinos
03-14-2010, 10:38 AM
Excellent!
Thank you for your help