Macro to count the number of cells in column that contain a Name and worn me if its more than 1 or zero

[tonywatsonhelp]

Hi Everyone,

Ok so i'm looking for a macro to do a check for me,
I have a sheet called FP Raw and it has a list of names,
I shouldn't have any duplicates but sometimes this happens and i want a simple macro to check this for me.

So when the macro is run i need it to do this:

Get a name from sheet "Welcome" range "ac57"

count how many times that name is found in sheet "FP raw" column Q

If its 0 times then

messagebox " That name does not exist"
exit sub
if its greater then 1 then

messagebox " The name appears ore then once please check data before proceeding"
exit sub

 

[MrTomasz]

try this:

Sub test()
    Dim lastrow As Long, i As Integer
    With Sheets("FP raw")
        lastrow = .Range("Q" & .Rows.Count).End(xlUp).Row
        i = Application.WorksheetFunction.CountIf(.Range("Q1:Q" & lastrow), Sheets("Welcome").Range("AC57").Value)
        If i = 0 Then
            MsgBox "That name does not exist"
        ElseIf i > 1 Then
            MsgBox "The name appears more then once please check data before proceeding"
        End If
    End With
End Sub

 

[jmacleary]

You don't actually need it to be a macro, for instance you could have this setup: (assuming AD57 is available) which is just the formula as used in MrTomasz answer but put into a cell

Book1
	AC	AD
57	Fred Bloggs	The name appears more than once please check data before proceeding
Welcome

Cell Formulas
Range	Formula
AD57	AD57	=IF(COUNTIF('FP Raw'!Q:Q,Welcome!AC57)=0,"That name does not exist",IF(COUNTIF('FP Raw'!Q:Q,Welcome!AC57)>1,"The name appears more than once please check data before proceeding","OK"))

 

[tonywatsonhelp]

Thank you MrTomasz, this does exactly what i needed,
Thank you John, but i need it to pop up as a message box otherwise it will get missed. but thank you for the help.
Thanks Tony