Multiple Ranges and Find/Replace

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Spare - 31 Mar 2008 15:33 GMT

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

Reply to this Message

Dave Peterson - 31 Mar 2008 15:54 GMT

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.)

> Could this be shortened to one block with something like, ("clr1",
> "clr2", ..). Taking it a step further, could the block be replaced
[quoted text clipped - 16 lines]
> If cell = "0" Then cell.Formula = "=NA()"
> Next cell

Signature

Dave Peterson

Reply to this Message