losing Application.FindFormat.NumberFormat after finding first cel

J

Jim at Eagle

Why would I lose the findformat after the find finds the first cell meeting
the findformat. With one loop it's on the proper cell. But then it wants to
play with everything after that, but still within the proper range. I tried
placing the format everwhere except in the front yard.
Any help would be appreciated. Even stupid help might promt my mind out of
this ditch.
Thanks

With rngSearch
'NumberFormat = Accounting
Application.FindFormat.NumberFormat = "_($* #,##0.00_);_($*
(#,##0.00);_($* ""-""??_);_(@_)"
Set C = .Cells.Find(What:="", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)
If Not C Is Nothing Then
firstC = C.Address
Do
C.ClearContents
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstC
End If
End With
 
D

Dave Peterson

I don't think that .findnext() remembers those settings.

I just keep doing the find over and over and over...

Here's a version that may help:
Option Explicit
Sub testme()

Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = .Range("a1:a25")
End With

With Application.FindFormat.Font
.Strikethrough = True
.Superscript = False
.Subscript = False
End With

With myRng
Set FoundCell = .Cells.Find(what:="", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
searchformat:=True)

If FoundCell Is Nothing Then
MsgBox "None found"
Else
FirstAddress = FoundCell.Address

Do

'do something to foundcell here??
MsgBox FoundCell.Address

Set FoundCell = .Cells.Find(what:="", _
after:=FoundCell, _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
searchformat:=True)

If FoundCell Is Nothing Then
Exit Do
End If

If FoundCell.Address = FirstAddress Then
Exit Do
End If

Loop

End If
End With
End Sub
 
J

Jim at Eagle

That worked. This is not the first time you help was the best.
Should be back in town this week. Let you know were lunch is.
Jim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top