Previous Topic Next Topic

Excel - Search column for condition

Closed
Matey - Dec 13, 2010 at 07:06 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 15, 2010 at 08:29 PM
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?
Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 15, 2010 at 08:29 PM
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
0