PDA

View Full Version : Solved: if value less then 5 show warning in worksheet change



mperrah
09-16-2007, 09:12 PM
I have a sub that works with a click,
I'm trying to make it automatic with sheet value changes in the column "D"

This is the worksheet_change I'm debugging...
Private Sub Worksheet_Change(ByVal target As Range)
Const TCOL As String = "D"
Dim CheckCells
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long

With Worksheets("Parts")

iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row

CheckCells = Worksheets("Parts").Range("D:D") ' this should be d1 to last row

If target.Cells.Count > 1 Then Exit Sub
If Not Intersect(target, CheckCells) Is Nothing Then ' trouble here

For i = 1 To iLastRow
Select Case Cells(i, TCOL).Value
Case 1 To 5
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"
Case " "
Case Else
End Select
Next i

End If
End With
End Sub

This is the sub that works
Sub lowInventory()
Const TCOL As String = "D"
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long

With Worksheets("Parts")

iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row
For i = 1 To iLastRow
Select Case Cells(i, TCOL).Value
Case 1 To 5
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"
Case " "
Case Else
End Select
Next i
End With

End Sub

Bob Phillips
09-17-2007, 12:22 AM
Private Sub Worksheet_Change(ByVal target As Range)
Const TCOL As String = "D"
Dim CheckCells As Range
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long

With Worksheets("Parts")

iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row

Set CheckCells = Worksheets("Parts").Range("D:D") ' this should be d1 to last row

If target.Cells.Count > 1 Then Exit Sub
If Not Intersect(target, CheckCells) Is Nothing Then ' trouble here

For i = 1 To iLastRow
Select Case Cells(i, TCOL).Value
Case 1 To 5
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"
Case " "
Case Else
Cells(i, TCOL).Offset(, 1).Value = ""
End Select
Next i

End If
End With
End Sub

mperrah
09-17-2007, 06:58 AM
Man, you got it goin' on !
Thank you
That did it.

Mark

mperrah
09-17-2007, 07:08 AM
One more thing,
How can I set this so column "E" is the min quantity
have "D" compare to "E"
Maybe set a variable each loop through the row (minVar)
in the case select use:
case select
Select Case Cells(i, TCOL).Value
Case minVar
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"

or with out changing the variable
Select Case Cells(i, TCOL).Value
Case Cells(i, TCOL).Value =< Cells(i, TCOL).offset(i, 1)
Cells(i, TCOL).Offset(, 2).Value = "Inventory Low"

Bob Phillips
09-17-2007, 08:27 AM
Private Sub Worksheet_Change(ByVal target As Range)
Const TCOL As String = "D"
Dim CheckCells As Range
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long

With Me

iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row

Set CheckCells = Me.Range("D:D") ' this should be d1 to last row

If target.Cells.Count > 1 Then Exit Sub
If Not Intersect(target, CheckCells) Is Nothing Then ' trouble here

For i = 1 To iLastRow
If Cells(i, TCOL).Value < Cells(i, TCOL).Offset(, 1).Value Then
Cells(i, TCOL).Offset(, 2).Value = "Inventory Low"
Else
Cells(i, TCOL).Offset(, 2).Value = ""
End If
Next i

End If
End With
End Sub

mperrah
09-17-2007, 10:15 AM
Sub youRock()
dim xld as kingofXL
dim i as integer

set xld = theman_themyth_thelegend

with allQuestionsUnderTheSun
for i to xld
if problem not is nothing then
xld = theSolution
else if
msg "A solution does not exist"
end if
next i
end with
end sub



(Thank you)