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-questions/963576-macro-find-specific-words-replace-them-then-highlight-anything-not-dictionary.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:

http://www.mrexcel.com/forum/images/icons/icon1.png 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. :D

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