| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help!!!!!!
Hi all
I've got a problem which has me tearing my hair out. I have a macro which uses a Range.replace function to change non-English characters to English ones, according to a set of predefined values, an example of one of the calls in this macro is : rRange.Replace What:="Á", Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, MatchByte:=True. It should find all the "Á" characters in the selected range and replace them with "A" but it doesn't do anything. In office 97 on a windows NT machine this worked. In Office XP on a windows XP machine in doesn't work. Both US English systems. WHAT'S WRONG WITH THIS VBA CODE. To add insult to injury. If a select a group of cells in excel choose Edit -Replace and instruct the application to replace "Á" with "A" it works. If however, I record a macro of me using choose Edit -Replace and run it, THAT DOESN"T WORK. If I examine the macro I will see the expression to be replaced is presented as "?" Please help I have been trying to find the answer to this for nearly 6 months off and on and its now getting urgent. Regards and thankful for any insight can be given. Paul |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help!!!!!!
Hi Paul,
Try: rRange.Replace What:=Chr(193), _ Replacement:="A", _ LookAt:=xlPart, _ SearchOrder:=xlByColumns --- Regards, Norman "Paul hampson" wrote in message ... Hi all I've got a problem which has me tearing my hair out. I have a macro which uses a Range.replace function to change non-English characters to English ones, according to a set of predefined values, an example of one of the calls in this macro is : rRange.Replace What:="", Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, MatchByte:=True. It should find all the "" characters in the selected range and replace them with "A" but it doesn't do anything. In office 97 on a windows NT machine this worked. In Office XP on a windows XP machine in doesn't work. Both US English systems. WHAT'S WRONG WITH THIS VBA CODE. To add insult to injury. If a select a group of cells in excel choose Edit -Replace and instruct the application to replace "" with "A" it works. If however, I record a macro of me using choose Edit -Replace and run it, THAT DOESN"T WORK. If I examine the macro I will see the expression to be replaced is presented as "?" Please help I have been trying to find the answer to this for nearly 6 months off and on and its now getting urgent. Regards and thankful for any insight can be given. Paul |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help!!!!!!
Thanks Norman but I get the same results as before that is there is no
replacement of the characters rRange.Replace What:=Chr(193), _ Replacement:="A", _ LookAt:=xlPart, _ SearchOrder:=xlByColumns --- Regards, Norman I think it is either to do with windows or office set ups but I can't see what to change. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help!!!!!!
Hi Paul,
I used Chr(193) which worked for me. As a suggestion, on the problematic system, verify the code, e.g.: copy the character of interest into A1 and in B1 enter = Code(A1) to return the corresponding code number. --- Regards, Norman "Paul hampson" wrote in message ... Thanks Norman but I get the same results as before that is there is no replacement of the characters rRange.Replace What:=Chr(193), _ Replacement:="A", _ LookAt:=xlPart, _ SearchOrder:=xlByColumns --- Regards, Norman I think it is either to do with windows or office set ups but I can't see what to change. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help!!!!!!
Thanks for the reply Norman.
I used the =code () fucntion that you suggested but all 60 characters came up with the code "63". To check I had done it correctly I add "XXX" (as these are English characters) and got 88 for that cell. As you understand, there is a problem as the VBA does not seem to recognize non English characters. At the moment the only work around I have is to create a macro in Open Office and use that. This is obviously not a good route to go, but it seems to work. But I really need to trace the source of this problem because it may have other repercussions that I haven't traced, and since I work on mission critical data ,this is important. I have tried the code on several machines at our company not one of them works. Below is the complete code: Sub GISCO_subs_BSI() Dim rRange As Range Set rRange = Selection.Cells rRange.Replace What:=Chr(192), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(193), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(194), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(195), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(196), Replacement:="AE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(197), Replacement:="AA", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(198), Replacement:="AE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(199), Replacement:="C", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(200), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(201), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(202), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(203), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(204), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(205), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(206), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(207), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(208), Replacement:="D", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(209), Replacement:="N", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(210), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(211), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(212), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(214), Replacement:="OE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(216), Replacement:="OE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(217), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(218), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(219), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(220), Replacement:="UE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(221), Replacement:="Y", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(222), Replacement:="P", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(223), Replacement:="SS", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(224), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(225), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(226), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(227), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(228), Replacement:="AE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(229), Replacement:="AA", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(230), Replacement:="AE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(231), Replacement:="C", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(232), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(233), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(234), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(235), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(236), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(237), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(238), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(239), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(240), Replacement:="D", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(241), Replacement:="N", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(242), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(243), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(244), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(245), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(246), Replacement:="OE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(248), Replacement:="OE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(249), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(250), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(251), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(252), Replacement:="UE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(253), Replacement:="Y", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(254), Replacement:="P", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(255), Replacement:="Y", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False End Sub Any guidnace you can offer is gratefully accepted. Regards Paul Hampson "Norman Jones" wrote: Hi Paul, I used Chr(193) which worked for me. As a suggestion, on the problematic system, verify the code, e.g.: copy the character of interest into A1 and in B1 enter = Code(A1) to return the corresponding code number. --- Regards, Norman "Paul hampson" wrote in message ... Thanks Norman but I get the same results as before that is there is no replacement of the characters rRange.Replace What:=Chr(193), _ Replacement:="A", _ LookAt:=xlPart, _ SearchOrder:=xlByColumns --- Regards, Norman I think it is either to do with windows or office set ups but I can't see what to change. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help!!!!!!
Hi Paul,
Try using the ChrW function, e.g.: rRange.Replace What:=ChrW(193), _ Replacement:="A", _ LookAt:=xlPart, _ SearchOrder:=xlByColumns To determine the individual character codes see Chip Pearson's CellView addin: http://www.cpearson.com/excel/CellView.htm --- Regards, Norman "Paul hampson" wrote in message ... Thanks for the reply Norman. I used the =code () fucntion that you suggested but all 60 characters came up with the code "63". To check I had done it correctly I add "XXX" (as these are English characters) and got 88 for that cell. As you understand, there is a problem as the VBA does not seem to recognize non English characters. At the moment the only work around I have is to create a macro in Open Office and use that. This is obviously not a good route to go, but it seems to work. But I really need to trace the source of this problem because it may have other repercussions that I haven't traced, and since I work on mission critical data ,this is important. I have tried the code on several machines at our company not one of them works. Below is the complete code: Sub GISCO_subs_BSI() Dim rRange As Range Set rRange = Selection.Cells rRange.Replace What:=Chr(192), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(193), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(194), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(195), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(196), Replacement:="AE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(197), Replacement:="AA", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(198), Replacement:="AE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(199), Replacement:="C", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(200), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(201), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(202), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(203), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(204), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(205), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(206), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(207), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(208), Replacement:="D", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(209), Replacement:="N", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(210), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(211), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(212), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(214), Replacement:="OE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(216), Replacement:="OE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(217), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(218), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(219), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(220), Replacement:="UE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(221), Replacement:="Y", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(222), Replacement:="P", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(223), Replacement:="SS", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(224), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(225), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(226), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(227), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(228), Replacement:="AE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(229), Replacement:="AA", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(230), Replacement:="AE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(231), Replacement:="C", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(232), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(233), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(234), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(235), Replacement:="E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(236), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(237), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(238), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(239), Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(240), Replacement:="D", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(241), Replacement:="N", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(242), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(243), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(244), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(245), Replacement:="O", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(246), Replacement:="OE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(248), Replacement:="OE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(249), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(250), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(251), Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(252), Replacement:="UE", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(253), Replacement:="Y", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(254), Replacement:="P", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False rRange.Replace What:=Chr(255), Replacement:="Y", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False End Sub Any guidnace you can offer is gratefully accepted. Regards Paul Hampson "Norman Jones" wrote: Hi Paul, I used Chr(193) which worked for me. As a suggestion, on the problematic system, verify the code, e.g.: copy the character of interest into A1 and in B1 enter = Code(A1) to return the corresponding code number. --- Regards, Norman "Paul hampson" wrote in message ... Thanks Norman but I get the same results as before that is there is no replacement of the characters rRange.Replace What:=Chr(193), _ Replacement:="A", _ LookAt:=xlPart, _ SearchOrder:=xlByColumns --- Regards, Norman I think it is either to do with windows or office set ups but I can't see what to change. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help!!!!!!
Thanks Norman That did the job. Paul "Norman Jones" wrote: Hi Paul, Try using the ChrW function, e.g.: rRange.Replace What:=ChrW(193), _ Replacement:="A", _ LookAt:=xlPart, _ SearchOrder:=xlByColumns T |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
