Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Ranges and Find/Replace
Could this be shortened to one block with something like, ("clr1",
"clr2", ..). Taking it a step further, could the block be replaced with a single find/replace statement? I'm having trouble getting the find/replace syntax correct. For Each cell In Sheet1.range("clr1").Cells If cell = "0" Then cell.Formula = "=NA()" Next cell For Each cell In Sheet1.range("clr2").Cells If cell = "0" Then cell.Formula = "=NA()" Next cell For Each cell In Sheet1.range("clr3").Cells If cell = "0" Then cell.Formula = "=NA()" Next cell For Each cell In Sheet1.range("clr4").Cells If cell = "0" Then cell.Formula = "=NA()" Next cell |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Ranges and Find/Replace
Maybe something like:
Dim myRng As Range With Sheet1 Set myRng = Union(.Range("clr1"), .Range("clr2"), .Range("clr3")) End With myRng.Replace what:=0, replacement:="=Na()", _ lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False (I got lazy and stopped after clr3.) Spare wrote: Could this be shortened to one block with something like, ("clr1", "clr2", ..). Taking it a step further, could the block be replaced with a single find/replace statement? I'm having trouble getting the find/replace syntax correct. For Each cell In Sheet1.range("clr1").Cells If cell = "0" Then cell.Formula = "=NA()" Next cell For Each cell In Sheet1.range("clr2").Cells If cell = "0" Then cell.Formula = "=NA()" Next cell For Each cell In Sheet1.range("clr3").Cells If cell = "0" Then cell.Formula = "=NA()" Next cell For Each cell In Sheet1.range("clr4").Cells If cell = "0" Then cell.Formula = "=NA()" Next cell -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria Find & Replace | Excel Worksheet Functions | |||
multiple find and replace | Excel Worksheet Functions | |||
Multiple find mulitple replace | Excel Worksheet Functions | |||
Find/Replace in multiple worksheets | Excel Discussion (Misc queries) | |||
Multiple Find and Replace in one function | Excel Worksheet Functions |