PDA

View Full Version : Solved: Find / FindNext question



JimmyTheHand
01-03-2007, 01:34 AM
Hi,

I'm trying to find all cells in Column A that are formatted Bold and Underlined.
The macro in the attachment should do exactly that.
A far as I know, it should find cell A4, then at FindNext, wrap around and exit the loop.
Instead, at Find method it finds A4, and at successive FindNext methods it keeps finding A16, A1, A2, A16, A1, A2, and so on, in an infinite loop. It never even gets back to A4! Not to mention that A1, A2 and A16 don't have the format that was specified to look for.

I'm looking for an explanation, why is that so.

EDIT ON
Please note: This problem didn't stop me from writing the code I wanted, because I have found a workaround (which was kindly shared below, by Andy Pope), so it's not an issue. I'm looking for answer to the question above.
EDIT OFF

Thanks,

Jimmy

Andy Pope
01-03-2007, 02:48 AM
Hi,

Not exactly sure why but the .findnext seems to be causing the problem.
Try this modification.
Sub Extract()
Dim Src As Worksheet
Dim FirstAddress As String
Dim c As Range

With Application.FindFormat.Font
.FontStyle = "Bold"
.Underline = xlUnderlineStyleSingle
End With

For Each Src In ThisWorkbook.Sheets
With Src.Range("A:A")
Set c = .Find(What:="", LookIn:=xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
'do something here
Set c = .Find(What:="", after:=c, LookIn:=xlFormulas, lookat:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)
Loop While (Not c Is Nothing) And (c.Address <> FirstAddress)
End If
End With
Next Src
End Sub

JimmyTheHand
01-03-2007, 02:56 AM
Hi Andy :hi:

Thanks for your reply. I found exactly the same workaround.
However, I'm looking for the answer to the question: what's the matter with FindNext. Sorry for not being specific enugh. I think I'll edit the first post.

Best,

Jimmy

Andy Pope
01-03-2007, 03:31 AM
Not much in the way of explanation but at least the problem is known.
http://support.microsoft.com/kb/282151

JimmyTheHand
01-03-2007, 03:46 AM
Andy,

thanks for the link. If M$ doesn't know, then who should. If they don't even make efforts to fix their product (the problem has been known since Excel 2002, and now, 5 years later, they release Excel 2007 with the same error... :cuckoo: ), well, then I think that's the end of it.

Problem "s:roll:lved".

Jimmy

Bob Phillips
01-03-2007, 05:16 AM
thanks for the link. If M$ doesn't know, then who should. If they don't even make efforts to fix their product (the problem has been known since Excel 2002, and now, 5 years later, they release Excel 2007 with the same error... :cuckoo: ), well, then I think that's the end of it.

Come on now, you can't expect them to be adding new, 'necessary' functions and still bother about old problems!

JimmyTheHand
01-03-2007, 05:49 AM
Come on now, you can't expect them to be adding new, 'necessary' functions and still bother about old problems!
Sure. But if I expected them to neglect bugs and develop only new bells and whistles, and they actually lived up to my expectations, that would be equally frustrating :moosegrin
Best is not to expect anything at all :tongue: