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

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