Hi Everyone

Below are my codes that I use to clean my data coming from the internet.
They all work except the first one (Find all the commas "," and replace with a
period (.) )
Can anyone tell me why?

Option Explicit

Sub Clean_Data()

Range("J3:L60").Select
Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _
:=xlByColumns
Range("J3:L60").Select
Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns

Range("J3:L60").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns

' ALT-0160
Range("J3:L60").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns

End Sub

Regards
John

Maybe the values in the cells don't contain commas. Maybe they're just plain
old numbers formatted to show commas (either as the thousands separator or the
decimal point????).

John wrote:
>
> Hi Everyone
>
> Below are my codes that I use to clean my data coming from the internet.
> They all work except the first one (Find all the commas "," and replace with a
> period (.) )
> Can anyone tell me why?
>
> Option Explicit
>
> Sub Clean_Data()
>
> Range("J3:L60").Select
> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _
> :=xlByColumns
> Range("J3:L60").Select
> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> xlByColumns
>
> Range("J3:L60").Select
> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> xlByColumns
>
> ' ALT-0160
> Range("J3:L60").Select
> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> xlByColumns
>
> End Sub
>
> Regards
> John

--

Dave Peterson

ps

> Range("J3:L60").Select
> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> xlByColumns

could be re-written:

Range("J3:L60").Replace What:=chr(160), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns

You could drop the .select's from all your code. And the chr(160) is a little
more self-documenting.


John wrote:
>
> Hi Everyone
>
> Below are my codes that I use to clean my data coming from the internet.
> They all work except the first one (Find all the commas "," and replace with a
> period (.) )
> Can anyone tell me why?
>
> Option Explicit
>
> Sub Clean_Data()
>
> Range("J3:L60").Select
> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _
> :=xlByColumns
> Range("J3:L60").Select
> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> xlByColumns
>
> Range("J3:L60").Select
> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> xlByColumns
>
> ' ALT-0160
> Range("J3:L60").Select
> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> xlByColumns
>
> End Sub
>
> Regards
> John

--

Dave Peterson

Hi Dave
Thank you for your help.
The commas are decimal separators and I need to change it to a period.
I did try manually to type the commas and to see if it would remove them but to
no availed.
If I use the "Find/Replace" menu, I can make it work but I get an error message.
even if I get an error, it still makes the corrections.
Regards
John



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Maybe the values in the cells don't contain commas. Maybe they're just plain
> old numbers formatted to show commas (either as the thousands separator or the
> decimal point????).
>
> John wrote:
>>
>> Hi Everyone
>>
>> Below are my codes that I use to clean my data coming from the internet.
>> They all work except the first one (Find all the commas "," and replace with
>> a
>> period (.) )
>> Can anyone tell me why?
>>
>> Option Explicit
>>
>> Sub Clean_Data()
>>
>> Range("J3:L60").Select
>> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _
>> :=xlByColumns
>> Range("J3:L60").Select
>> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
>> xlByColumns
>>
>> Range("J3:L60").Select
>> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
>> xlByColumns
>>
>> ' ALT-0160
>> Range("J3:L60").Select
>> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
>> xlByColumns
>>
>> End Sub
>>
>> Regards
>> John
>
> --
>
> Dave Peterson

I use the dot for my decimal point and I could change that to a comma with no
trouble.

What error do you get?

Maybe you could try just changing the commas to nothing. Depending on how your
numbers are grouped, it may even be a better choice???

John wrote:
>
> Hi Dave
> Thank you for your help.
> The commas are decimal separators and I need to change it to a period.
> I did try manually to type the commas and to see if it would remove them but to
> no availed.
> If I use the "Find/Replace" menu, I can make it work but I get an error message.
> even if I get an error, it still makes the corrections.
> Regards
> John
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Maybe the values in the cells don't contain commas. Maybe they're just plain
> > old numbers formatted to show commas (either as the thousands separator or the
> > decimal point????).
> >
> > John wrote:
> >>
> >> Hi Everyone
> >>
> >> Below are my codes that I use to clean my data coming from the internet.
> >> They all work except the first one (Find all the commas "," and replace with
> >> a
> >> period (.) )
> >> Can anyone tell me why?
> >>
> >> Option Explicit
> >>
> >> Sub Clean_Data()
> >>
> >> Range("J3:L60").Select
> >> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _
> >> :=xlByColumns
> >> Range("J3:L60").Select
> >> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> >> xlByColumns
> >>
> >> Range("J3:L60").Select
> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> >> xlByColumns
> >>
> >> ' ALT-0160
> >> Range("J3:L60").Select
> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
> >> xlByColumns
> >>
> >> End Sub
> >>
> >> Regards
> >> John
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Hi Dave
I tried changing commas to nothing,does't work.
The error message is:
"The formula you typed contains an error."
For information about fixing common formula problems, click Help.
with two more line of information.
Regards
John

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I use the dot for my decimal point and I could change that to a comma with no
> trouble.
>
> What error do you get?
>
> Maybe you could try just changing the commas to nothing. Depending on how
> your
> numbers are grouped, it may even be a better choice???
>
> John wrote:
>>
>> Hi Dave
>> Thank you for your help.
>> The commas are decimal separators and I need to change it to a period.
>> I did try manually to type the commas and to see if it would remove them but
>> to
>> no availed.
>> If I use the "Find/Replace" menu, I can make it work but I get an error
>> message.
>> even if I get an error, it still makes the corrections.
>> Regards
>> John
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Maybe the values in the cells don't contain commas. Maybe they're just
>> > plain
>> > old numbers formatted to show commas (either as the thousands separator or
>> > the
>> > decimal point????).
>> >
>> > John wrote:
>> >>
>> >> Hi Everyone
>> >>
>> >> Below are my codes that I use to clean my data coming from the internet.
>> >> They all work except the first one (Find all the commas "," and replace
>> >> with
>> >> a
>> >> period (.) )
>> >> Can anyone tell me why?
>> >>
>> >> Option Explicit
>> >>
>> >> Sub Clean_Data()
>> >>
>> >> Range("J3:L60").Select
>> >> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder
>> >> _
>> >> :=xlByColumns
>> >> Range("J3:L60").Select
>> >> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart,
>> >> SearchOrder:= _
>> >> xlByColumns
>> >>
>> >> Range("J3:L60").Select
>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>> >> SearchOrder:= _
>> >> xlByColumns
>> >>
>> >> ' ALT-0160
>> >> Range("J3:L60").Select
>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>> >> SearchOrder:= _
>> >> xlByColumns
>> >>
>> >> End Sub
>> >>
>> >> Regards
>> >> John
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

Hi Dave
I changed the comma symbal , for Chr(44) and it seem to work.
Will need to play with it to be sure.
It's your idea with Chr(160) that made me try that.
Thanks Dave
John
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I use the dot for my decimal point and I could change that to a comma with no
> trouble.
>
> What error do you get?
>
> Maybe you could try just changing the commas to nothing. Depending on how
> your
> numbers are grouped, it may even be a better choice???
>
> John wrote:
>>
>> Hi Dave
>> Thank you for your help.
>> The commas are decimal separators and I need to change it to a period.
>> I did try manually to type the commas and to see if it would remove them but
>> to
>> no availed.
>> If I use the "Find/Replace" menu, I can make it work but I get an error
>> message.
>> even if I get an error, it still makes the corrections.
>> Regards
>> John
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Maybe the values in the cells don't contain commas. Maybe they're just
>> > plain
>> > old numbers formatted to show commas (either as the thousands separator or
>> > the
>> > decimal point????).
>> >
>> > John wrote:
>> >>
>> >> Hi Everyone
>> >>
>> >> Below are my codes that I use to clean my data coming from the internet.
>> >> They all work except the first one (Find all the commas "," and replace
>> >> with
>> >> a
>> >> period (.) )
>> >> Can anyone tell me why?
>> >>
>> >> Option Explicit
>> >>
>> >> Sub Clean_Data()
>> >>
>> >> Range("J3:L60").Select
>> >> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder
>> >> _
>> >> :=xlByColumns
>> >> Range("J3:L60").Select
>> >> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart,
>> >> SearchOrder:= _
>> >> xlByColumns
>> >>
>> >> Range("J3:L60").Select
>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>> >> SearchOrder:= _
>> >> xlByColumns
>> >>
>> >> ' ALT-0160
>> >> Range("J3:L60").Select
>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>> >> SearchOrder:= _
>> >> xlByColumns
>> >>
>> >> End Sub
>> >>
>> >> Regards
>> >> John
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

Me again
I tried it with fresh data 3 times and it works perfectly.
This is my new line:
Range("J3:L60").Replace what:=Chr(44), Replacement:=Chr(46), LookAt:=xlPart,
SearchOrder:= xlByColumns
Many thanks Dave
Regards
John
"John" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Dave
> I changed the comma symbal , for Chr(44) and it seem to work.
> Will need to play with it to be sure.
> It's your idea with Chr(160) that made me try that.
> Thanks Dave
> John
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I use the dot for my decimal point and I could change that to a comma with no
>> trouble.
>>
>> What error do you get?
>>
>> Maybe you could try just changing the commas to nothing. Depending on how
>> your
>> numbers are grouped, it may even be a better choice???
>>
>> John wrote:
>>>
>>> Hi Dave
>>> Thank you for your help.
>>> The commas are decimal separators and I need to change it to a period.
>>> I did try manually to type the commas and to see if it would remove them
>>> but to
>>> no availed.
>>> If I use the "Find/Replace" menu, I can make it work but I get an error
>>> message.
>>> even if I get an error, it still makes the corrections.
>>> Regards
>>> John
>>>
>>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> > Maybe the values in the cells don't contain commas. Maybe they're just
>>> > plain
>>> > old numbers formatted to show commas (either as the thousands separator or
>>> > the
>>> > decimal point????).
>>> >
>>> > John wrote:
>>> >>
>>> >> Hi Everyone
>>> >>
>>> >> Below are my codes that I use to clean my data coming from the internet.
>>> >> They all work except the first one (Find all the commas "," and replace
>>> >> with
>>> >> a
>>> >> period (.) )
>>> >> Can anyone tell me why?
>>> >>
>>> >> Option Explicit
>>> >>
>>> >> Sub Clean_Data()
>>> >>
>>> >> Range("J3:L60").Select
>>> >> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart,
>>> >> SearchOrder _
>>> >> :=xlByColumns
>>> >> Range("J3:L60").Select
>>> >> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart,
>>> >> SearchOrder:= _
>>> >> xlByColumns
>>> >>
>>> >> Range("J3:L60").Select
>>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>>> >> SearchOrder:= _
>>> >> xlByColumns
>>> >>
>>> >> ' ALT-0160
>>> >> Range("J3:L60").Select
>>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>>> >> SearchOrder:= _
>>> >> xlByColumns
>>> >>
>>> >> End Sub
>>> >>
>>> >> Regards
>>> >> John
>>> >
>>> > --
>>> >
>>> > Dave Peterson
>>
>> --
>>
>> Dave Peterson
>

I have no idea why that would matter.

John wrote:
>
> Me again
> I tried it with fresh data 3 times and it works perfectly.
> This is my new line:
> Range("J3:L60").Replace what:=Chr(44), Replacement:=Chr(46), LookAt:=xlPart,
> SearchOrder:= xlByColumns
> Many thanks Dave
> Regards
> John
> "John" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Dave
> > I changed the comma symbal , for Chr(44) and it seem to work.
> > Will need to play with it to be sure.
> > It's your idea with Chr(160) that made me try that.
> > Thanks Dave
> > John
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >>I use the dot for my decimal point and I could change that to a comma with no
> >> trouble.
> >>
> >> What error do you get?
> >>
> >> Maybe you could try just changing the commas to nothing. Depending on how
> >> your
> >> numbers are grouped, it may even be a better choice???
> >>
> >> John wrote:
> >>>
> >>> Hi Dave
> >>> Thank you for your help.
> >>> The commas are decimal separators and I need to change it to a period.
> >>> I did try manually to type the commas and to see if it would remove them
> >>> but to
> >>> no availed.
> >>> If I use the "Find/Replace" menu, I can make it work but I get an error
> >>> message.
> >>> even if I get an error, it still makes the corrections.
> >>> Regards
> >>> John
> >>>
> >>> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >>> news:(E-Mail Removed)...
> >>> > Maybe the values in the cells don't contain commas. Maybe they're just
> >>> > plain
> >>> > old numbers formatted to show commas (either as the thousands separator or
> >>> > the
> >>> > decimal point????).
> >>> >
> >>> > John wrote:
> >>> >>
> >>> >> Hi Everyone
> >>> >>
> >>> >> Below are my codes that I use to clean my data coming from the internet.
> >>> >> They all work except the first one (Find all the commas "," and replace
> >>> >> with
> >>> >> a
> >>> >> period (.) )
> >>> >> Can anyone tell me why?
> >>> >>
> >>> >> Option Explicit
> >>> >>
> >>> >> Sub Clean_Data()
> >>> >>
> >>> >> Range("J3:L60").Select
> >>> >> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart,
> >>> >> SearchOrder _
> >>> >> :=xlByColumns
> >>> >> Range("J3:L60").Select
> >>> >> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart,
> >>> >> SearchOrder:= _
> >>> >> xlByColumns
> >>> >>
> >>> >> Range("J3:L60").Select
> >>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
> >>> >> SearchOrder:= _
> >>> >> xlByColumns
> >>> >>
> >>> >> ' ALT-0160
> >>> >> Range("J3:L60").Select
> >>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
> >>> >> SearchOrder:= _
> >>> >> xlByColumns
> >>> >>
> >>> >> End Sub
> >>> >>
> >>> >> Regards
> >>> >> John
> >>> >
> >>> > --
> >>> >
> >>> > Dave Peterson
> >>
> >> --
> >>
> >> Dave Peterson
> >

--

Dave Peterson

Hi Dave
>I have no idea why that would matter.<
I wish someone knew, you're telling me it's a fluke,just hope it keeps working.
Regards
John


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have no idea why that would matter.
>
> John wrote:
>>
>> Me again
>> I tried it with fresh data 3 times and it works perfectly.
>> This is my new line:
>> Range("J3:L60").Replace what:=Chr(44), Replacement:=Chr(46), LookAt:=xlPart,
>> SearchOrder:= xlByColumns
>> Many thanks Dave
>> Regards
>> John
>> "John" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Hi Dave
>> > I changed the comma symbal , for Chr(44) and it seem to work.
>> > Will need to play with it to be sure.
>> > It's your idea with Chr(160) that made me try that.
>> > Thanks Dave
>> > John
>> > "Dave Peterson" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >>I use the dot for my decimal point and I could change that to a comma with
>> >>no
>> >> trouble.
>> >>
>> >> What error do you get?
>> >>
>> >> Maybe you could try just changing the commas to nothing. Depending on how
>> >> your
>> >> numbers are grouped, it may even be a better choice???
>> >>
>> >> John wrote:
>> >>>
>> >>> Hi Dave
>> >>> Thank you for your help.
>> >>> The commas are decimal separators and I need to change it to a period.
>> >>> I did try manually to type the commas and to see if it would remove them
>> >>> but to
>> >>> no availed.
>> >>> If I use the "Find/Replace" menu, I can make it work but I get an error
>> >>> message.
>> >>> even if I get an error, it still makes the corrections.
>> >>> Regards
>> >>> John
>> >>>
>> >>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> >>> news:(E-Mail Removed)...
>> >>> > Maybe the values in the cells don't contain commas. Maybe they're just
>> >>> > plain
>> >>> > old numbers formatted to show commas (either as the thousands separator
>> >>> > or
>> >>> > the
>> >>> > decimal point????).
>> >>> >
>> >>> > John wrote:
>> >>> >>
>> >>> >> Hi Everyone
>> >>> >>
>> >>> >> Below are my codes that I use to clean my data coming from the
>> >>> >> internet.
>> >>> >> They all work except the first one (Find all the commas "," and
>> >>> >> replace
>> >>> >> with
>> >>> >> a
>> >>> >> period (.) )
>> >>> >> Can anyone tell me why?
>> >>> >>
>> >>> >> Option Explicit
>> >>> >>
>> >>> >> Sub Clean_Data()
>> >>> >>
>> >>> >> Range("J3:L60").Select
>> >>> >> Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart,
>> >>> >> SearchOrder _
>> >>> >> :=xlByColumns
>> >>> >> Range("J3:L60").Select
>> >>> >> Cells.Replace What:="$", Replacement:="", LookAt:=xlPart,
>> >>> >> SearchOrder:= _
>> >>> >> xlByColumns
>> >>> >>
>> >>> >> Range("J3:L60").Select
>> >>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>> >>> >> SearchOrder:= _
>> >>> >> xlByColumns
>> >>> >>
>> >>> >> ' ALT-0160
>> >>> >> Range("J3:L60").Select
>> >>> >> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>> >>> >> SearchOrder:= _
>> >>> >> xlByColumns
>> >>> >>
>> >>> >> End Sub
>> >>> >>
>> >>> >> Regards
>> >>> >> John
>> >>> >
>> >>> > --
>> >>> >
>> >>> > Dave Peterson
>> >>
>> >> --
>> >>
>> >> Dave Peterson
>> >
>
> --
>
> Dave Peterson