replace VBA not working consistantly

[Oliver Dewar]

Hi All.

Fairly simple one this. I think I'm dealing with a bug in excel 2007 - I'm doing this replace sequence in vba across a sheet with many instances of the character to replace, the sheet includes some cells with large strings of text.

Here's my code:

ActiveSheet.Cells.Replace What:="§§", Replacement:=Chr(10)

Some of the "§§" instances are replaced while others - especially those within the large text strings, are not.

Does anyone know of a sure fire way to do this replacement? Speed is preferable... but right now I'm just after 100% reliability.


edit*** oohh, just found out about Lookat:=xlWhole and the other settings... perhaps I need to stipulate those settings more concretly. Going to do more research but if you can put me out of my misery faster then please do!


Thanks All.

Oliver

 

[Oliver Dewar]

Ok, Ok so just when I was sure I'd found a bonafide excel bug it turns out that the problems was (as Hal put it) "Human error".

This is the code I just ran:

Cells.Replace What:="§§", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

And it works. Seems that the changes you make in the manual search version is used again for the vba version - so you have to be specific in vba to reset.

Thanks for your moral support everyone

 

[Oliver Dewar]

Spoke too soon. Still not reliable.

Thinking again that excel simply doesn't handle large find/replaces well.

Since the data is going into a csv file I'm thinking that I could open the csv file in notepad and do the find replace there from excel. Anyone know how to do this?

 

[Oliver Dewar]

Wow - that's scary... the notepad idea was bad.

Back to the start.

Can anyone give me a ironclad bit of code that will replace all instances of a charachter in a worksheet?

I'm beaten at the moment. Need an excel hero!

 

[Oliver Dewar]

Hi Guys. Forget this - when you hit a dead end enough times... you change vehicle! I'm going to use excel sheets to do what what I was going to do with csv files instead.