Thread: Replacing spaces with zeros

Try: Selection.Replace instead of Cells.Replace

HTH
--
AP

"Oldjay" <Oldjay@discussions.microsoft.com> a écrit dans le message de news:
4E931E83-9C3F-4134-928C-CB853C867CED@microsoft.com...
>I have the following
>
> Sub FillZeros()
>
> Sheets("Records").Select
> Range("N2:N500").Select
> Application.CutCopyMode = False
> Cells.Replace What:="", Replacement:=0, LookAt:=xlPart, SearchOrder _
> :=xlByRows, MatchCase:=False
>
> End Sub
>
> When I run it I get 5 additional cols of zeros
>
> Oldjay

I have the following

Sub FillZeros()

Sheets("Records").Select
Range("N2:N500").Select
Application.CutCopyMode = False
Cells.Replace What:="", Replacement:=0, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

End Sub

When I run it I get 5 additional cols of zeros

Oldjay

Sub FillZeros()

Sheets("Records").Select
Range("N2:N500").Select
Application.CutCopyMode = False
Selection.Replace What:="", Replacement:=0, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

End Sub
--
Regards,
Tom Ogilvy


"Oldjay" wrote:

> I have the following
>
> Sub FillZeros()
>
> Sheets("Records").Select
> Range("N2:N500").Select
> Application.CutCopyMode = False
> Cells.Replace What:="", Replacement:=0, LookAt:=xlPart, SearchOrder _
> :=xlByRows, MatchCase:=False
>
> End Sub
>
> When I run it I get 5 additional cols of zeros
>
> Oldjay

> Cells.Replace What:="", Replacement:=0, LookAt:=xlPart, SearchOrder _
> :=xlByRows, MatchCase:=False

This statement replaces empty strings "".

Try

> Cells.Replace What:=" ", Replacement:=0, LookAt:=xlPart, SearchOrder _
> :=xlByRows, MatchCase:=False

but first check that you want to replace all spaces in all cells
(LookAt:=xlPart) or only those standing alone in a cell (LookAt:=xlWhole)

Regards,
Stefi

„Oldjayâ€� ezt Ã*rta:

> I have the following
>
> Sub FillZeros()
>
> Sheets("Records").Select
> Range("N2:N500").Select
> Application.CutCopyMode = False
> Cells.Replace What:="", Replacement:=0, LookAt:=xlPart, SearchOrder _
> :=xlByRows, MatchCase:=False
>
> End Sub
>
> When I run it I get 5 additional cols of zeros
>
> Oldjay

Thanks everybody

"Ardus Petus" wrote:

> Try: Selection.Replace instead of Cells.Replace
>
> HTH
> --
> AP
>
> "Oldjay" <Oldjay@discussions.microsoft.com> a écrit dans le message de news:
> 4E931E83-9C3F-4134-928C-CB853C867CED@microsoft.com...
> >I have the following
> >
> > Sub FillZeros()
> >
> > Sheets("Records").Select
> > Range("N2:N500").Select
> > Application.CutCopyMode = False
> > Cells.Replace What:="", Replacement:=0, LookAt:=xlPart, SearchOrder _
> > :=xlByRows, MatchCase:=False
> >
> > End Sub
> >
> > When I run it I get 5 additional cols of zeros
> >
> > Oldjay
>
>
>