Macro question

[Learner79]

HELP
'Need to EXCLUDE Workbook "Project.xlsm" when searching
'For some reason it is still displaying YES when nothing is found


Sub FindName()

Dim i As Long
Dim Fnd As String
Dim fCell As Range
Dim ws As Worksheet


Fnd = "#NAME"

For Each ws In Worksheets
With ws
Set fCell = .Range("A1")
For i = 1 To WorksheetFunction.CountIf(.Cells, Fnd)
Set fCell = .Cells.Find(What:=Fnd, After:=fCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then


Range("=Coded!$C$13").Value = "None"
Range("=Coded!$C$13").Interior.Color = RGB(0, 192, 0)


Exit For
Else
With fCell
Range("=Coded!$C$13").Value = "Yes"
Range("=Coded!$C$13").Interior.Color = RGB(255, 0, 0)



End With
End If
Next i
End With
Next ws

End Sub

[Sisyphus]

Hi,
I find nothing wrong with your code and do not understand your initial demand to exclude workbook Project.xlsm. That workbook isn't mentioned in your code, and I don't think it influences the performance of your code. What I did find is that the worksheet name "=Coded" causes a crash, but then I was testing in Excel 2003. I renamed the worksheet as "Coded" and had no more problems thereafter.

While looking at your code I noted that you count the number of occurrences of Fnd and then let your loop run precisely that number of times. Of course, the case that nothing is found that you provide for in some detail can't occur. If nothing is found the entire For .. Next loop doesn't run and, therefore, your message that there are no #NAME errors will never be issued.

On the other hand, you might avoid writing the same "Found" message in the same cell in the Coded sheet as many times as Fnd exists if you would Exit For after the first find. In fact, it may have been this feature of your code that suggested to you that sightings of Fnd were still being registered after the last fCell. The crash occurs after, yes, but after the FIRST find, more precisely, at the time of trying to write to Range("=Coded!$C$13").