VBA if value isn't in a range

[melewie]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’m having trouble with a piece of code to identify if the value of a cell is a value in a range.<o></o>

I have a range 'Country_Code_Rng' which contains a list of countries

I want to loop through and identify any contries that are not within this list on a database. something like

NumRows = Cells(65000, 1).End(xlUp).Row
ColNo = 9
    For RowNo = 1 To NumRows
        If Cells(RowNo, ColNo).Value <> [B][COLOR=red]a value in range Country_Code_Rng[/COLOR] [/B]then
 
            Cells(RowNo, ColNo).EntireRow.Copy
            NewWB.Activate
            Worksheets("Major Space Data").Select
            Cells(65000, 1).End(xlUp).Offset(1, 0).Select
            ActiveSheet.Paste
            wBookExec.Activate
            Sheets("Major Data").Activate
 
        End If
 
    Next RowNo

Any help would be great

 

[SteveO59L]

Why not use a native function like vlookup() ?. Then manually copy and paste?

 

[VoG]

Try

If IsError(Application.Match(Cells(RowNo, ColNo).Value, Range("Country_Code_Rng"), 0)) Then

 

[melewie]

Thanks VoG - works a treat as always