Find in Named Range problem (2nd Try)
 

Hello. I'm using Excel X on a Mac (VBA5?) and attempting to write a macro
which names a range of text cells I've selected, then searches the cells in
that range and bolds any which contain the "" character (as text, not as
"greater than" in a formula). I'm not using conditional formatting because I
also want to get rid of the characters once the bolding is complete. In
stepping through the following, I find that it does name the range and does
the finding and bolding in a loop. Unfortunately it doesn't stop at the end
of the range, but continues down the entire column. I always stop it there
so I do not know if the last "find and replace" section works at all. I've
tried everything I can find, so any help would be appreciated. I apologize
for the many REM statements, but they help me keep it straight in my head as
I am building it. Also the indenting did not paste with the code, so I've
tried to recreate it here.

Sub NLFI_Find_Recc_Char_Bold_Loop()
'
' Before running this, select a range of cells with
' Cntl+Shift+DwnArrow. This is made a named range below
'
Selection.Name = "ReccFunds_Range"
'
' Loop through that range to Find character and bold
' contents of cells containing it
'
For Each Cell In Range("ReccFunds_Range")
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
With ActiveCell.Characters(Start:=1, Length:=0).Font
.FontStyle = "Bold"
End With
Next Cell
'
' Next section globally finds all in range and replaces them with nothing
'
Range("ReccFunds_Range").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=
_False).Activate
Selection.Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
End Sub

Find in Named Range problem (2nd Try)
 

Change this (your big problem is using Cells (with an S), not Cell)

For Each Cell In Range("ReccFunds_Range")
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
With ActiveCell.Characters(Start:=1, Length:=0).Font
..FontStyle = "Bold"
End With
Next Cell

to

For Each Cell In Range("ReccFunds_Range")
If InStr(1, Cell.Value, "") 0 Then
Cell.Font.Bold = True
End If
Next Cell

And then, change this

' Next section globally finds all in range and replaces them with nothing
'
Range("ReccFunds_Range").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=
_False).Activate
Selection.Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False

to just

Range("ReccFunds_Range").Replace What:="", Replacement:="", LookAt:=xlPart

HTH,
Bernie
MS Excel MVP


"G.R." wrote in message
...
Hello. I'm using Excel X on a Mac (VBA5?) and attempting to write a macro
which names a range of text cells I've selected, then searches the cells in
that range and bolds any which contain the "" character (as text, not as
"greater than" in a formula). I'm not using conditional formatting because I
also want to get rid of the characters once the bolding is complete. In
stepping through the following, I find that it does name the range and does
the finding and bolding in a loop. Unfortunately it doesn't stop at the end
of the range, but continues down the entire column. I always stop it there
so I do not know if the last "find and replace" section works at all. I've
tried everything I can find, so any help would be appreciated. I apologize
for the many REM statements, but they help me keep it straight in my head as
I am building it. Also the indenting did not paste with the code, so I've
tried to recreate it here.

Sub NLFI_Find_Recc_Char_Bold_Loop()
'
' Before running this, select a range of cells with
' Cntl+Shift+DwnArrow. This is made a named range below
'
Selection.Name = "ReccFunds_Range"
'
' Loop through that range to Find character and bold
' contents of cells containing it
'
For Each Cell In Range("ReccFunds_Range")
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
With ActiveCell.Characters(Start:=1, Length:=0).Font
.FontStyle = "Bold"
End With
Next Cell
'
' Next section globally finds all in range and replaces them with nothing
'
Range("ReccFunds_Range").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=
_False).Activate
Selection.Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
End Sub

Find in Named Range problem (2nd Try)
 

Bernie,
Your solution worked like a charm.
I'm very grateful for your help, and trying not to be embarrassed at how
basic my Find mistake was. Also thank you for the more elegant Replace code.

G.R.