Excel - Search column for condition [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hey Guys

I would be very grateful for some assistance.

I have a list of names (ie. John, Josh, Paul, Peter, Rick) i expect to be in a column however occasionally a new name will appear in the column .

I wish to create a macro that will search this column (A2 to A500) and have a msgbox pop telling all the names on the list other than John, Josh, Paul, Peter, Rick (ie..new to the column).

There could be quite a lot of names, so i think it is best to create an array with the names? and use a find/boolean query somehow??

Any ideas?

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
It would be helpful to help you if all the information is given.

you have not told where the list of names like john etc are parked. You also did not say where you will be entering the names .
let us suppose it is parked in cell J3 and down with no gap. You can have as many names in the row as you like. If the list is not in this range modify the event code in the code statemnt
set r=...........................................


I have taken the names are entered in column A with no header row.
if it is in different column modify the line
if target.column <>.......................................

open vb editor (alt+F11). click control+R. in the project all open files are listed including hidden files. go to the relevant file and right click "sheet1" and click view code

in the window that comes up copy this event code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cfind As Range, r As Range, x As String
If Target.Column <> 1 Then Exit Sub
x = Target.Value
Set r = Range(Range("J3"), Range("J3").End(xlDown))
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then MsgBox "this new entry is not available in the list"

End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!