PDA

View Full Version : Replacing in VBA



ukdane
03-19-2009, 11:37 AM
This seems odd to me, but is probably simple for the Gurus to solve.

I have a workbook, that someone has sent me.
In one of the sheets is some text, which has been added from another program.

During the conversion, the letters Æ,Ø,Å have been replaced by a BOX. I recorded a marco which changed each instance of these, in the attempt to automate the process. (using the replace function). When I recorded the marco, it worked fine. the Æ boxes were replaced with Æ, the Ø boxes replaced with Ø and the Å boxes replaced with Å.

BUT when I looked at the VBA and rerun the Macro, the boxes had been replaced with ? and when run, the replace funtion replaced every character with the last letter in the code (in this case Æ).

When I just tried to copy the boxes in here (including examples of the words containing them, this is what I saw:
リ for Ø
ナ for Å
and ニ for Æ

If I try to copy these back into VBA, they are returned as ? again.

What code do I need to write in order for the correct letters to be replaced? Would it involve ANSI?

Help appreciated, as always.

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

mdmackillop
03-19-2009, 11:40 AM
You can use the CODE function on your spreadsheet to determine the characters used. =CODE(A1) will give the first character, or you can used CODE with MID to check others.

ukdane
03-19-2009, 12:01 PM
Using code returns all 3 as "63" ie they are all the same.
But the aren't.

mdmackillop
03-19-2009, 12:19 PM
Can you post a sample? There is always the possibility of non-printing characters distorting things.

ukdane
03-19-2009, 12:43 PM
Try this one (not sure it'll 'not' work)

Sheet1 cells A1:A3 contain words including the box (each one should be replaced by one of the following Æ,Ø,Å).

If you copy the box from each line to a message here, you should be able to see the "ODD" signs.

If you run the CODE it'll return 63.

If you run CHAR(63) it returns a ?

In the VBA, there is a recorded macro to replace the letters, except that what I recorded was replaced with a ?.

If you actually copy the box from each line, into a replace, and select one of the letters above it will only replace the correct letter (for example
ULSNニS where ニ is a box should be replaced with Æ).

What do you think?

Cheers

mdmackillop
03-19-2009, 01:04 PM
It's got me stumped. You cannot simply replace "?" as this is used as a wild card. To replace a real ?, you search and replace ~? However, I can't get a result for that either.

ukdane
03-24-2009, 12:37 AM
Can anyone see a solution "from outside the box"?

I've lucked out, so far.