I have also had issues when I alter the visibility (hidding or grouping) of
rows/columns just before using find.
I am now getting a similar issue in a different sub that does not use Find:
Range("K1").Select
Selection.EntireColumn.Insert
Range("K1").Value = "Transfer not closed"
SARow = 2
Do
ErrMsg = ""
If Cells(SARow, 6) = "Unique" Then
If Cells(SARow, 47) = "" Then
Cells(SARow, 11) = "Transfer not closed"
End If
SARow = SARow + 1
Else
StudentsLastRow = 0
While Cells(SARow, 5) = Cells(SARow + StudentsLastRow, 5)
StudentsLastRow = StudentsLastRow + 1 ' When loop stops,
StudentsLastRow equals the number of
' casefiles for the student
Wend
StudentsLastRow = SARow + StudentsLastRow - 1 ' Now it equals the
student's last row number
For Index = SARow To StudentsLastRow
' If any transfer row is not closed, store errmsg
If Cells(Index, 16) = 4 Or _
Cells(Index, 16) = 5 Then
If Cells(Index, 47) = "" Then
ErrMsg = "Transfer not closed"
End If
End If
Next Index
If ErrMsg = "Transfer not closed" Then
' Write error msg to every row for the student
For Index = SARow To StudentsLastRow
Cells(Index, 11) = ErrMsg
Next Index
End If
SARow = StudentsLastRow + 1
End If
Loop Until SARow > LastSARow
-------
In this case the code finds no instances of the exception condition when it
runs free, but stepping through on specific row numbers where I know that the
issue exists somehow enables the code to find the problem.
Again, if there's bottlenecking going on, why doesn't it occur more
consistently, throughout my code?
I'm at SP 2 of Excel 2003. Maybe there's a patch for this, but I wouldn't
know how to search for it.
<rant on>
I SO should have gone to the trouble to get direct read-only access to the
database and written these reports in an SQL environment. The learning time
to enhance my SQL knowledge sufficiently would have been nothing next to all
this wild goose/ghost chasing. The need to do it seems ridiculous for such a
supposedly mature product. And how can I or my customers now ever trust the
results of this code? It _sometimes_ finds the error condition and sometimes
doesn't, so we can't even use a complete absence of any cases of the error as
a trouble flag.<rant off>
Probably the fastest way to fix this would be to use filtering instead of
direct comparison to find the rows that meet the exception criteria, and
write the exception to every visible row. But that's evading the issue.