Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Row in a named range | Excel Worksheet Functions | |||
Find Cell in Named Range | Excel Worksheet Functions | |||
VBA: find number of columns in named range? | Excel Discussion (Misc queries) | |||
Named Range name problem in validation | Excel Worksheet Functions | |||
How to find all formulas that used a certain named range | Excel Discussion (Misc queries) |