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
|