I like to use a Countif function.

If Worksheetfunction.Countif(Range("D:D") ,EmployeeName) = 0 then ' or
Range("D10:D160")
msgbox "Employee not in list"
else
msgbox "Employee already listed"
End If

--
steveB

Remove "AYN" from email to respond
"Applewine" wrote
in message ...

I have a range of data in D10:D160
Using an InputBox a new employee name is entered
This name is checked against the range for a duplicate
I keep getting an error "Loop without Do" with this code
I suspect I am losing the Do call by exiting a For:Next loop....?


Msg = "Enter new employee name:"
EmployeeName = InputBox(Msg, vbCancel)
If EmployeeName = "" Then Exit Sub 'cancel data entry & exit
Do
For i = 10 To 160 'check range
If ActiveSheet.Cells(i, 4) = EmployeeName Then
Msg = "There is already an employee with that name...(please add a last
name or an initial)"
Msg = Msg &vbNewLine& "Enter new employee name:"
EmployeeName = InputBox(Msg, vbCancel)
If EmployeeName = "" Then Exit Sub 'cancel data entry & exit
Loop
Next i
End If

I have tried many different variations of this...don't know if I should
abandon this approach (but something tells me it will work if i can
avoid nesting pitfall).

Thank you for your time!

-Dave


--
Applewine
------------------------------------------------------------------------
Applewine's Profile:
http://www.excelforum.com/member.php...fo&userid=5512
View this thread: http://www.excelforum.com/showthread...hreadid=397585