Replace works not in recorded VBA

**rpinxt** · 12-31-2014, 07:23 AM

Hi all,
I recorde a macro to replace my commas in amount with dots.

Columns("AB:AB").Select
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Works manually while recording it ok.
But when I run the recorded macro it will not replace anything....

**davesexcel** · 12-31-2014, 07:47 AM

Your code works for me, here is is cleaned up a bit.

    Columns("AB:AB").SpecialCells(xlCellTypeConstants, 23).Replace _
            What:=",", Replacement:=".", _
            SearchOrder:=xlByColumns, MatchCase:=True

If you are trying to change the commas in an actual number, you would have to format the cells as text first.

**rpinxt** · 12-31-2014, 08:01 AM

Thanks but it does nothing for me.....
Also when I first change the format...it still keeps showing "," and not "."

**rpinxt** · 12-31-2014, 08:25 AM

Let me sent a sample which does not work for me.
My Office settings are Dutch so decimals separator is ",".

**bart hoekstra** · 12-31-2014, 08:48 AM

Hi

Run it in Xcel2007. It works fine here.

bart

**TMS** · 12-31-2014, 09:35 AM

The data in column A in your sample is numeric. With my regional settings, the decimal part is separated by a full stop (dot). In yours, it may be a comma (,).

If I do a global replace (using your code) and change full stop to comma, it works but the data becomes text where there is a decimal part. I can then reverse the process and it becomes numeric again.

Nothing wrong with the code as it stands.

Regards, TMS

Replace works not in recorded VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Replace works not in recorded VBA

Re: Replace works not in recorded VBA

Re: Replace works not in recorded VBA

Re: Replace works not in recorded VBA

Re: Replace works not in recorded VBA

Re: Replace works not in recorded VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Recorded macro doing more than recorded?

Replace Macro recorded code with more effective code

[SOLVED] FormulaArray 255 charter limitation .Replace workaround no-longer works/unstable

[SOLVED] Recorded Pivot Table Macro Doesn't Do What I Recorded!

Find and replace recorded macro messes up the dates

Bookmarks

Bookmarks

Posting Permissions