Checking for Duplicate entry using VBA (?!)

[Applewine]

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

[UofMoo]

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."
End If
EmployeeName = InputBox(Msg2 & " " & Msg1, vbCancel)
While EmployeeName <> ""

[Applewine]

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

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

Thanks for your help

Dave

[Applewine]

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

[STEVE BELL]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>