Set ws = ActiveSheet Compile error Can't find project or Library
I have a macro that searches a worksheet for certain text then fills
the cell with a specified color. I had help from this newsgroup a few
years back to write it and it has worked perfectly since then...until
yesterday.
When I run the macro I get an error and it drops me into a debugger
hightlighting the ws in the statement
Set ws = ActiveSheet
The error says "Compile error: Can't find project or Library"
Is it possible that this is related to a patch that got applied?
Any help would be appreciated.
Here is the full Macro
Sub FindHiLight_V2()
'I8 Color19
'Get search values from worksheet range.
Dim MyFind As Variant
Dim MyNewValue As Variant
Dim FoundCell As Object
Dim Counter As Long
Dim searchList As Range
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I8")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 19
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
' I9 Color49
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I9")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 49
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I10 Color22
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I10")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 22
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I11 Color7
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I11")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 7
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I12 Color3
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I12")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 3
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I13 Color5
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I13")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 5
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I14 Color24
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I14")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 24
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I15 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I15")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I16 Color4
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I16")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 4
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I17 Color55
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I17")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 55
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I18 Color46
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I18")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 46
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I15 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I19")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I20 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I20")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I21 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I21")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I22 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I22")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I23 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I23")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I24 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I24")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I25 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I25")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I26 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I26")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I27 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I27")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I29 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I28")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I29 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I29")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I30 Color6
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I30")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 6
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
'I31 Color3
'-------------------------------------------------
'- SET SEARCH KEY
'===== Change Range in next statement as needed =======
Set searchList = Worksheets("Instructions").Range("I31")
For Each MyFind In searchList
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 3
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)
Next 'MyFind
End Sub
|