find and replace

Posted by Andonny on June 15, 2001 3:48 AM

Hi,
I need to find all the empty cells and replace them with a "0". I have the
code below to do that. The problem I am having is that it should only do
that in column A based on the last cell used in column B.

Sub FindReplace()
Range("A1:A100").Replace What:="", Replacement:="0", LookAt:=xlPart, _
MatchCase:=False
End Sub

Thank you for your help
Andonny

Posted by JAF on June 15, 2001 4:14 AM

Hiya - the code you need is as follows:

Sub RevisedFindReplace()
Range(("B1"), Range("B1").End(xlDown)).Offset(0, -1).Replace What:="", Replacement:="0", LookAt:=xlPart, _
MatchCase:=False
End Sub

NB: This assumes that all cells in column B are completed. If not, let me know and I'll come up with something else!

JAF

Posted by JAF on June 15, 2001 4:19 AM

Revised again...

This will fill every blank cell in Column A down to the last completed cell in Column B even if some of the Column B cells are blank:

Sub RevisedAgainFindReplace()
Range([B65536].End(xlUp).Cells(1, 0), [A1]).Replace What:="", Replacement:="0", LookAt:=xlPart, MatchCase:=False
End Sub

JAF

Posted by Andonny on June 15, 2001 5:01 AM

Re: Thanks a million

Hi JAF,

Thank you very much for your kind help
Andonny