How to search column and highlight values that are not in dictionary?

[joshman1088]

I would like to keep a list of words within my macro (not in cells in workbook) which would be referenced as a dictionary. I then want to search column A against dictionary and if A values are NOT in dictionary then highlight those cells. I would also like to ignore blank cells.

How may I accomplish this? I would like to add to this dictionary as needed.
Values will never be over 30 characters. There may sometimes be spaces in between values in cells, so "word word bla"). I would like it to be non-case sensitive? How many I accomplish this? I have found examples online for workbooks, not columns. I don't know how to alter them yet.

Thanks! I'm trying to learn more vba as I go.

[SamT]

Find is not case sensitive.

Why a Dictionary Object? A List is more straight forward coding and of comparable speed.

For Dictionary method:

Dim MyDictionary as Object

Sub InitDictionary()
'The list
'And
'Code to add each word in the list to myDictionary
End Sub

Sub UseDictionary()
Dim Cel As Range

If myDictionary Is Nothing Then InitDictionary

For Each Cel in Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
'Pseudocode for several lines of code I would have to look up.
If not Cel in Dictionary then
'end pseudocode
Cel.Interior.Colorindex = 3
End If 
Next cel
End Sub

For List method

Sub List()
'Complete code, just edit ranges
Dim Cel As Range
Dim Found as Range

'Sheets("SomeSheet").Range("A:A") = List of words

For Each Cel in Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
Set Found = Sheets("SomeSheet").Range("A:A").Find(Cel)
If Found Is Nothing Then Cel.Interior.Colorindex = 3
Next cel
End Sub

[joshman1088]

I'm getting a syntax error on


"If Not Cel In dictionary Then"

I just used the word dictionary because it was the first word that came to mind based on my limited understanding.

[SamT]

Read the comments. BTW, Speedy, I edited that post, probably after you read it the first time.

I would have to look up the use of Dictionary objects online, since all my self made help files are sitting in a dead computer.

An Alternative to a Worksheet list is an array

Sub Use Array()
Dim Cel As Range
Dim i as Long
Dim InList As Boolean
Dim myList as variant
myList = Array("list", "of", Words", "here")

For Each Cel In Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)) 
        For i = LBound(myList) to Ubound(myList)
        If LCase(Cel.Value) = Lcase(myList(i)) Then 
          InList = True
          Exit For
       End If
        Next i
If Not InList then Cel.Interior.Colorindex = 3 
    Next cel 
End Sub

[jolivanes]

http://www.mrexcel.com/forum/excel-q...ictionary.html

[joshman1088]

Sam, from your first post, the second code seems to do nothing. How do you get it to highlight values not found in the list?
Also your second post just gives me "compile error: end of statement" at the "Sub Use Array" bit.

[SamT]

From your link:

Re: Macro to find specific words and replace them, and then highlight anything that is not in dictionary

That succeeded in replacing the words I indicated. It applied to 1500 rows and took about 45 seconds. There will be instances where I am dealing with up to 10,000 rows perhaps, and I need this to take much less than 45 seconds. Is it possible to change that? A simple find and replace with ctrl+f takes about a second. Not to be ungrateful by any means!

That is a different scenario than you posted here. Bad Dog, No biscuit for you.

You still need the two lists on a sheet, ("WordsList".)
Starting in Row 1, put the keywords in column A and their replacements in column B.

Sub Use_ Array_2() 
    Dim Cel As Range 
    Dim i As Long 
    Dim InList As Boolean 
    Dim myList As Variant 

    myList = Sheets("WordsList").Range("A1").CurrentRegion.Value
'If that fails use
'    myList = Transpose(Sheets("WordsList").Range("A1").CurrentRegion.Value)
'OR
'    myList = Transpose(Sheets("WordsList").Range("A1").CurrentRegion).Value

'Application.ScreenUpdating = False 'Uncomment after testing
     
    For Each Cel In Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)) 
        For i = LBound(myList) To Ubound(myList) 
            If LCase(Cel.Value) = Lcase(myList(i,1)) Then 
                Cel = myList(i, 2)
                InList = True 
                Exit For 
            End If 
        Next i 
        If Not InList Then Cel.Interior.Colorindex = 3 
    Next cel 

Application.ScreenUpdating = True
End Sub

[joshman1088]

Yes I understand it is a different request. I figured it would be easier to just ask for the one part, I found a bit that can handle the replacement. I guess I should have clarified? And as I said, I need to have a list in the macro, not on the workbook, so your most recent example doesn't work.

[SamT]

It is faster to Loop thru MyList and Find and Replace on the other sheet, BUT, I don't see how to highlight the un-replaced words

Sub Use_ Array_3() 
    Dim i As Long 
    Dim myList As Variant 
     
    myList = Sheets("WordsList").Range("A1").CurrentRegion.Value 
     'If that fails use
     '    myList = Transpose(Sheets("WordsList").Range("A1").CurrentRegion.Value)
     'OR
     '    myList = Transpose(Sheets("WordsList").Range("A1").CurrentRegion).Value
     
     'Application.ScreenUpdating = False 'Uncomment after testing
     
        For i = LBound(myList) To Ubound(myList) 
         Replace(Range("A:A"), myList(i, 1), mylist(i, 2))   
        Next i 
      
    Application.ScreenUpdating = True 
End Sub

What would you need after replacing all the words with that Procedure.

[Paul_Hossler]

I would like to keep a list of words within my macro (not in cells in workbook) which would be referenced as a dictionary. I then want to search column A against dictionary and if A values are NOT in dictionary then highlight those cells. I would also like to ignore blank cells.

How may I accomplish this? I would like to add to this dictionary as needed.
Values will never be over 30 characters. There may sometimes be spaces in between values in cells, so "word word bla"). I would like it to be non-case sensitive? How many I accomplish this? I have found examples online for workbooks, not columns. I don't know how to alter them yet.

Thanks! I'm trying to learn more vba as I go.

This is what you asked for, but I think a hidden worksheet with the OK words would be better and easier to maintain

Option Explicit
Sub FlagEntries()
    Dim aWords(1 To 100) As String
    Dim i As Long
    Dim rWords As Range, rWord As Range
    
    aWords(1) = "AAA"
    aWords(2) = "BBB"
    aWords(3) = "CCC"
    aWords(4) = "DDD ddd"
    aWords(5) = "eee eee eee"
    aWords(6) = "FFF"
    aWords(7) = "GGG"
    aWords(8) = "HHH"
    
    Application.ScreenUpdating = False
    For Each rWord In ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues)
        i = 0
        On Error Resume Next
        i = Application.WorksheetFunction.Match(rWord.Value, aWords, 0)
        On Error GoTo 0
        
        If i = 0 Then rWord.Interior.Color = vbRed
    Next
    Application.ScreenUpdating = True
End Sub