Problem using replace in macro

[01Patrik]

Hi,

Have a problem using replace in a macro. I have no education at all in VB, everything i do is "self taught". This means that I rather include recorded code when possible, since I'm not a very competent code writer myself.


The problem is that the recorded code doesn't work...

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

(I'm trying to remove the comas in spreadsheet with figures - note that it works fine to replace the coma with nothing with ctrl+H)

All help appriciated greatly...

// Patrik

 

[xlbo]

works fine for me - are you sure the commas aren't part of the format rather than actual commas .... Do you still see the comma if you select the cell and look in the formula bar ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[01Patrik]

Yep.

And it works fine when I record the macro (using ctrl + H) - but not when playing it.

// Patrik

 

[xlbo]

I just copied your code verbhatim, put it into a sub and ran it - worked fine. What version of excel are you running ?? (I'm on XP)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[01Patrik]

Me too.


Found the problem now...: The data is copied from a web page table, via Word (to put it in a table - if I paste directly to Excel the rows and cols are messed up) and into Excel from there.

If I enter a text including a coma the macro works fine.


So I avoid the problem by inserting a number of "text to columns" - thounsandsseparator:=",".

This works fine and removes all comas.



Thanks for your help though.

// Patrik

 

[01Patrik]

eehh... forget what I just said. That worked fine whilst recording, as well, but not at all while playing.

// Patrik