Check text in a range

  • Thread starter Thread starter mlhynes
  • Start date Start date
M

mlhynes

Hi all

I’m a new member & I’d appreciate help with the following:

I have a vertical range of text which changes and which is linked t
another sheet. I need to be alerted if a text appears more than tw
times in the range. Any takers??

e.g.

Bob
Bob
Tom
Tom
Jane
Jane
Bob

“alert, a member is oversubscribed”

Thanks
Mik
 
I’m a new member & I’d appreciate help with the following:
I have a vertical range of text which changes and which is linked to
another sheet. I need to be alerted if a text appears more than two
times in the range. Any takers??
Click to expand...
This may be a job for conditional formatting, use 'formula is':
=COUNTIF($A:$A;A1)>2 (I assume your range starts in A1).
 
This will tell you each time there is more than one in the list
Sub morethanonce()
For Each c In Selection
If Application.CountIf(Selection, c) > 1 Then
MsgBox c & " is oversubscribed"
End If
Next
End Sub
 
Thanks people for your help so far. I'm nearly there and excuse me i
the solution is obvious. However I still can't quiet get it to work.
The vertical text range changes but I need to be alerted if "any typ
of text" appears more than once.

Your have helped point me in the right direction so far and I d
appreciate your time and help

Regards
Mik
 
Back
Top