G
Guest
I am having problems getting a For Each-Next statement to work the way I want
with my 3 conditions. I have an Expense report that on each row I want to
validate that a trip # (column E) is entered if a date is entered (col B) AND
the Sum of Expense rows (M, L & N) is greater than zero. My code (below) is
close but it has one flaw I am having trouble figuring out how to correct. I
want my validations to run on each row independantly and it is not doing this
properly.
For example, if row 20 does not have a trip number but meets the conditions
of not needing one (sum=0), the code will allow me to proceed to row 21. If
row 21 has a trip number, date & Sum is greater than zero, the code will take
me back to row 20 with the missing trip number.
Can anyone help me fix this code to correct this problem? Any help is
greatly appreciated.
======== CODE ======
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("b20:N39")) Is Nothing Then
Dim c As Variant
For Each c In Worksheets("Expense Report").Range("b20:b39").Cells
If c <> "" And IsEmpty(Cells(c.Row, "E")) Then
Sum = 0
Sum = Sum + Sheet1.Cells(Target.Row, "M").Value
Sum = Sum + Sheet1.Cells(Target.Row, "L").Value
Sum = Sum + Sheet1.Cells(Target.Row, "N").Value
If Sum > 0 Then
Sheet1.Cells(Target.Row, "E").Select
MsgBox "Trip # is required when reporting Travel
Expenses."
Exit For
End If
End If
Next
End If
End Sub
with my 3 conditions. I have an Expense report that on each row I want to
validate that a trip # (column E) is entered if a date is entered (col B) AND
the Sum of Expense rows (M, L & N) is greater than zero. My code (below) is
close but it has one flaw I am having trouble figuring out how to correct. I
want my validations to run on each row independantly and it is not doing this
properly.
For example, if row 20 does not have a trip number but meets the conditions
of not needing one (sum=0), the code will allow me to proceed to row 21. If
row 21 has a trip number, date & Sum is greater than zero, the code will take
me back to row 20 with the missing trip number.
Can anyone help me fix this code to correct this problem? Any help is
greatly appreciated.
======== CODE ======
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("b20:N39")) Is Nothing Then
Dim c As Variant
For Each c In Worksheets("Expense Report").Range("b20:b39").Cells
If c <> "" And IsEmpty(Cells(c.Row, "E")) Then
Sum = 0
Sum = Sum + Sheet1.Cells(Target.Row, "M").Value
Sum = Sum + Sheet1.Cells(Target.Row, "L").Value
Sum = Sum + Sheet1.Cells(Target.Row, "N").Value
If Sum > 0 Then
Sheet1.Cells(Target.Row, "E").Select
MsgBox "Trip # is required when reporting Travel
Expenses."
Exit For
End If
End If
Next
End If
End Sub