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

Hi there...
Hope this helps... I assume you want a continually running loop until
the user enters "" as the name... you'll have to check if the vbCancel
works because nowhere does it check for that response. Good Luck!
UofMoo



Msg1 = "Enter new employee name:"
EmployeeName = InputBox(Msg, vbCancel)
If EmployeeName = "" Then Exit Sub
EndIf

Do
For i = 10 To 160
If ActiveSheet.Cells(i, 4) = EmployeeName Then
Msg2 = "There is already an employee with that name..."
i = 161
Exit For
EndIf
Next
If i<161 Then Msg2 = "That was a new name."
EmployeeName = InputBox(Msg2 & " " & Msg1, vbCancel)
While EmployeeName < ""


--
UofMoo
------------------------------------------------------------------------
UofMoo's Profile: http://www.excelforum.com/member.php...o&userid=26485
View this thread: http://www.excelforum.com/showthread...hreadid=397585

Actually, what would I have to change if i want the loop to ru
continually until the user enters an employee name that is unique (no
found in the range)?

I have tried to amend your answer in diferent ways including using
new variable if there is a duplicate found, but can't quite get it al
together (!)

Thanks for your help

Dav

--
Applewin
-----------------------------------------------------------------------
Applewine's Profile: http://www.excelforum.com/member.php...nfo&userid=551
View this thread: http://www.excelforum.com/showthread.php?threadid=39758

Msg = "Enter new employee name:"
EmployeeName = InputBox(Msg, vbCancel)
If EmployeeName = "" Then Exit Sub '

Do
Flag = 0
Msg = "Enter new employee name:"
For i = 10 To 160
If ActiveSheet.Cells(i, 4) = EmployeeName Then Flag = 1
Next
If Flag = 1 Then Msg = "There is already an employee by that name..try
again:"
If Flag = 1 Then EmployeeName = InputBox(Msg, vbCancel)
Loop While Flag = 1

Thanks, Moo, for your help!

-Dave


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

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