Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Set ws = ActiveSheet Compile error Can't find project or Library
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default Set ws = ActiveSheet Compile error Can't find project or Library
sometimes errors liek this are misleading as they result from a completely
different, though non-the-less very serious problem.

Open TOOLS / REFERENCES from the VBA editor menu and check to see if any
DLLs appear as MISSING
This could happen with an install/update

if any are, you'll need to browse to them, or, worst case, fix your excel
installation.


"Rick" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Set ws = ActiveSheet Compile error Can't find project or Library
Problem duplicates on multiple PC's. I also fully uninstalled, then
reinstalled Office 2007 std on this machine and tested before allowing
any updates. Same result.



On Oct 27, 12:19*pm, Patrick Molloy
wrote:
sometimes errors liek this are misleading as they result from a completely
different, though non-the-less very serious problem.

Open TOOLS / REFERENCES *from the VBA editor menu and check to see if any
DLLs appear as MISSING
This could happen with an install/update

if any are, you'll need to browse to them, or, worst case, fix your excel
installation.



"Rick" wrote:
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 =

...

read more »- Hide quoted text -

- Show quoted text -

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Set ws = ActiveSheet Compile error Can't find project or Library
I not see a dim line for ws

For the OP

Add this line

Dim ws As Worksheet




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Patrick Molloy" wrote in message
...
sometimes errors liek this are misleading as they result from a completely
different, though non-the-less very serious problem.

Open TOOLS / REFERENCES from the VBA editor menu and check to see if any
DLLs appear as MISSING
This could happen with an install/update

if any are, you'll need to browse to them, or, worst case, fix your excel
installation.


"Rick" wrote:

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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Set ws = ActiveSheet Compile error Can't find project or Library
Thank you.
That moved me along a little.
Now the debugger stops at
FirstAddress = FoundCell.Address
with "FirstAddress" highlighted.
Maybe I need a Dim staement for that as well?


On Oct 27, 4:22*pm, "Ron de Bruin" wrote:
I not see a dim line for ws

For the OP

Add *this line

Dim ws As Worksheet

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Patrick Molloy" wrote in message

...



sometimes errors liek this are misleading as they result from a completely
different, though non-the-less very serious problem.

Open TOOLS / REFERENCES *from the VBA editor menu and check to see if any
DLLs appear as MISSING
This could happen with an install/update

if any are, you'll need to browse to them, or, worst case, fix your excel
installation.

"Rick" wrote:

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

...

read more »- Hide quoted text -

- Show quoted text -

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Set ws = ActiveSheet Compile error Can't find project or Library
Yes you must Dim them all

Dim FirstAddress As String




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rick" wrote in message ...
Thank you.
That moved me along a little.
Now the debugger stops at
FirstAddress = FoundCell.Address
with "FirstAddress" highlighted.
Maybe I need a Dim staement for that as well?


On Oct 27, 4:22 pm, "Ron de Bruin" wrote:
I not see a dim line for ws

For the OP

Add this line

Dim ws As Worksheet

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Patrick Molloy" wrote in message

...



sometimes errors liek this are misleading as they result from a completely
different, though non-the-less very serious problem.

Open TOOLS / REFERENCES from the VBA editor menu and check to see if any
DLLs appear as MISSING
This could happen with an install/update

if any are, you'll need to browse to them, or, worst case, fix your excel
installation.

"Rick" wrote:

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

...

read more »- Hide quoted text -

- Show quoted text -

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Set ws = ActiveSheet Compile error Can't find project or Library
And yet another....
In the statement
rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
"Found: " & Counter)

It highlights the first "Chr"

I must apologize for my ignorance here. I didn't write the
macro....Do I need to Dim Chr as well? as a string?

I am still kind of reeling from what happened to the macro as it
worked fine for 2 years.

Anyway...thanks for the ongoing assistantance.

rick

On Oct 27, 4:50*pm, "Ron de Bruin" wrote:
Yes you must Dim them all

Dim FirstAddress As String

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Rick" wrote in ...

Thank you.
That moved me along a little.
Now the debugger stops at
FirstAddress = FoundCell.Address
with "FirstAddress" highlighted.
Maybe I need a Dim staement for that as well?

On Oct 27, 4:22 pm, "Ron de Bruin" wrote:



I not see a dim line forws

For the OP

Add this line

DimwsAs Worksheet

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Patrick Molloy" wrote in message

...

sometimes errors liek this are misleading as they result from a completely
different, though non-the-less very serious problem.

Open TOOLS / REFERENCES from the VBA editor menu and check to see if any
DLLs appear as MISSING
This could happen with an install/update

if any are, you'll need to browse to them, or, worst case, fix your excel
installation.

"Rick" wrote:

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 thewsin the statement

Setws=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
Setws=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
Setws=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
Setws=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
Setws=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
Setws=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
Setws=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
Setws=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
Setws=ActiveSheet
Set FoundCell =ws.Cells.Find(what:=MyFind)
If Not

...

read more »- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compile error: Can't find project or library Melanie New Users to Excel 14 August 21st 09 02:54 AM
Compile Error: Can't Find Project or Library Albert Browne Excel Programming 2 September 16th 08 09:05 PM
Compile Error: Can't find project or library J.B. Excel Programming 3 March 28th 08 08:18 PM
Compile error - Can't find project library ???? [email protected] Excel Programming 0 October 30th 07 08:18 PM
Compile error: Can't find project or library Vince Excel Programming 2 October 13th 05 04:50 PM


All times are GMT +1. The time now is 07:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement