show result in msg box

J

Jared

I created a macro which checks for duplicates entries:
I would like the macro to display a message box with the name found and then
requst if wanted to be deleted. I can't seem to be able to put in the name in
the msgbox.

thanks, jared

Sub Check_4_Dups()
Dim first As String
Dim Second As String
Dim i As Integer
Dim Response


For i = 3 To 52
first = Cells(i, 4).Value
If first <> "Employee's Name" Then
For n = i + 1 To 52
Second = Cells(n, 4).Value
If Second <> "Employee's Name" Then
If first = Second Then Response = MsgBox("The Name "
&First& "Appears Twice, Do you want to delete one entry?", vbYesNo, "Check
for Duplicate names")
If Response = vbYes Then Cells(n, 4) = "Employee's Name"
End If
Next n
End If
Next i
End Sub
 
C

Carim

Hi,

It looks as if your "Employee's Name" is a variable which is not
defined ...

HTH
Carim
 
J

Jared

"employee's name" is not a variable, "first" is the variable. Wherever a name
is not listed
it doesn't show a blank space, it shows "employee's name"
i hope that covers your question.

jared
 
B

Bob Phillips

Worked fine for me, but this is more efficient

Sub Check_4_Dups()
Dim first As String
Dim i As Long
Dim Response

For i = 3 To 52
first = Cells(i, 4).Value
If first <> "Employee's Name" Then
If Application.CountIf(Range("D3", Cells(i, 4)), first) > 1 Then
Response = MsgBox("The Name " _
& first & " appears Twice, Do you want to delete
one entry?", _
vbYesNo, "Check for Duplicate names")
If Response = vbYes Then Cells(i, 4) = "Employee's Name"
End If
End If
Next i
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
J

Jared

That's Right.
I did not know exactly how to make a Countif formula for this one.

But Thanks
 
Top