I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above
program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

Can you build your string in japan using the chr() function? Obviously, I
didn't try it on my computer;)

PA

"TKS_Mark" wrote:

I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above
program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

What is the chr() function? Unfortunately, my programming experience it
limited to recording macros and trying to modify them slightly. If you give
me a hint, I can probably experiment and figure it out.

"PA" wrote:

Can you build your string in japan using the chr() function? Obviously, I
didn't try it on my computer;)

PA

"TKS_Mark" wrote:

I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above
program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

the chr() function is the same as the character function in Excel (not sure
of the name, I have french Excel). To get help on it, type chr in the VBA
editor and press F1.

Basicly, what it does is to return a character based on a number (0-255). So

Chr(74) & Chr(97) & Chr(112) & Chr(97) & Chr(110)

will return "Japan"

Explore a bit on it. Maybe you can return character from a different set of
letters (Japan).

Give me some news.

PA

"TKS_Mark" wrote:

What is the chr() function? Unfortunately, my programming experience it
limited to recording macros and trying to modify them slightly. If you give
me a hint, I can probably experiment and figure it out.

"PA" wrote:

Can you build your string in japan using the chr() function? Obviously, I
didn't try it on my computer;)

PA

"TKS_Mark" wrote:

I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above
program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

On the Japanese computer, the code for 集 is char(15672). But on the English
computer, char(15672) returns #VALUE!. And when I use the function
=code("集") on the English computer, it returns the answer 63. But when I
write =char(63) (or chr(63) in a macro) on the English computer, it returns
the question mark character "?".

Any ideas? It seems the chr results are different depending on the
computer's language.

"PA" wrote:

the chr() function is the same as the character function in Excel (not sure
of the name, I have french Excel). To get help on it, type chr in the VBA
editor and press F1.

Basicly, what it does is to return a character based on a number (0-255). So

Chr(74) & Chr(97) & Chr(112) & Chr(97) & Chr(110)

will return "Japan"

Explore a bit on it. Maybe you can return character from a different set of
letters (Japan).

Give me some news.

PA

"TKS_Mark" wrote:

What is the chr() function? Unfortunately, my programming experience it
limited to recording macros and trying to modify them slightly. If you give
me a hint, I can probably experiment and figure it out.

"PA" wrote:

Can you build your string in japan using the chr() function? Obviously, I
didn't try it on my computer;)

PA

"TKS_Mark" wrote:

I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above
program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

Well at this point, let's go back to basics. In your first post, you said
that the indirect formula return #REF. Can we fix that formula to make sure
it work in japan and avoid the find and replace?

What is your original formula and what is your currupt formula? How's the
sheet built?

PA

"TKS_Mark" wrote:

On the Japanese computer, the code for 集 is char(15672). But on the English
computer, char(15672) returns #VALUE!. And when I use the function
=code("集") on the English computer, it returns the answer 63. But when I
write =char(63) (or chr(63) in a macro) on the English computer, it returns
the question mark character "?".

Any ideas? It seems the chr results are different depending on the
computer's language.

"PA" wrote:

the chr() function is the same as the character function in Excel (not sure
of the name, I have french Excel). To get help on it, type chr in the VBA
editor and press F1.

Basicly, what it does is to return a character based on a number (0-255). So

Chr(74) & Chr(97) & Chr(112) & Chr(97) & Chr(110)

will return "Japan"

Explore a bit on it. Maybe you can return character from a different set of
letters (Japan).

Give me some news.

PA

"TKS_Mark" wrote:

What is the chr() function? Unfortunately, my programming experience it
limited to recording macros and trying to modify them slightly. If you give
me a hint, I can probably experiment and figure it out.

"PA" wrote:

Can you build your string in japan using the chr() function? Obviously, I
didn't try it on my computer;)

PA

"TKS_Mark" wrote:

I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above
program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

The original formula was =INDIRECT("_"&AreaCde&"C[[#Totals],["&[ActCde]&"]]")
It builds the reference to another range based on the text in same column as
this cell but in the range called "AreaCde". It also uses information from
the same row but in a range called "ActCde". I need to translate [#Totals]
to [#集計].

"PA" wrote:

Well at this point, let's go back to basics. In your first post, you said
that the indirect formula return #REF. Can we fix that formula to make sure
it work in japan and avoid the find and replace?

What is your original formula and what is your currupt formula? How's the
sheet built?

PA

"TKS_Mark" wrote:

On the Japanese computer, the code for 集 is char(15672). But on the English
computer, char(15672) returns #VALUE!. And when I use the function
=code("集") on the English computer, it returns the answer 63. But when I
write =char(63) (or chr(63) in a macro) on the English computer, it returns
the question mark character "?".

Any ideas? It seems the chr results are different depending on the
computer's language.

"PA" wrote:

the chr() function is the same as the character function in Excel (not sure
of the name, I have french Excel). To get help on it, type chr in the VBA
editor and press F1.

Basicly, what it does is to return a character based on a number (0-255). So

Chr(74) & Chr(97) & Chr(112) & Chr(97) & Chr(110)

will return "Japan"

Explore a bit on it. Maybe you can return character from a different set of
letters (Japan).

Give me some news.

PA

"TKS_Mark" wrote:

What is the chr() function? Unfortunately, my programming experience it
limited to recording macros and trying to modify them slightly. If you give
me a hint, I can probably experiment and figure it out.

"PA" wrote:

Can you build your string in japan using the chr() function? Obviously, I
didn't try it on my computer;)

PA

"TKS_Mark" wrote:

I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above
program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

Honestly, I don't understand all the brackets []. Anyway, I prefer using the
offset function rather then indirect. Can you send an exemple of your
formula? Espacially with the final reference built and definition of your
range name. Do you use R1C1 notation for your formula? do you use Column
label in your formula?

I'm sorry that I don't have a quick answer for you.

PA

"TKS_Mark" wrote:

The original formula was =INDIRECT("_"&AreaCde&"C[[#Totals],["&[ActCde]&"]]")
It builds the reference to another range based on the text in same column as
this cell but in the range called "AreaCde". It also uses information from
the same row but in a range called "ActCde". I need to translate [#Totals]
to [#集計].

"PA" wrote:

Well at this point, let's go back to basics. In your first post, you said
that the indirect formula return #REF. Can we fix that formula to make sure
it work in japan and avoid the find and replace?

What is your original formula and what is your currupt formula? How's the
sheet built?

PA

"TKS_Mark" wrote:

On the Japanese computer, the code for 集 is char(15672). But on the English
computer, char(15672) returns #VALUE!. And when I use the function
=code("集") on the English computer, it returns the answer 63. But when I
write =char(63) (or chr(63) in a macro) on the English computer, it returns
the question mark character "?".

Any ideas? It seems the chr results are different depending on the
computer's language.

"PA" wrote:

the chr() function is the same as the character function in Excel (not sure
of the name, I have french Excel). To get help on it, type chr in the VBA
editor and press F1.

Basicly, what it does is to return a character based on a number (0-255). So

Chr(74) & Chr(97) & Chr(112) & Chr(97) & Chr(110)

will return "Japan"

Explore a bit on it. Maybe you can return character from a different set of
letters (Japan).

Give me some news.

PA

"TKS_Mark" wrote:

What is the chr() function? Unfortunately, my programming experience it
limited to recording macros and trying to modify them slightly. If you give
me a hint, I can probably experiment and figure it out.

"PA" wrote:

Can you build your string in japan using the chr() function? Obviously, I
didn't try it on my computer;)

PA

"TKS_Mark" wrote:

I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above
program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

TKS_Mark wrote:
On the Japanese computer, the code for 集 is char(15672). But on the English
computer, char(15672) returns #VALUE!. And when I use the function
=code("集") on the English computer, it returns the answer 63. But when I
write =char(63) (or chr(63) in a macro) on the English computer, it returns
the question mark character "?".

Any ideas? It seems the chr results are different depending on the
computer's language.

I am afraid you are in out of your depth here. The Japanese language is
represented in a PC by double byte character sets so your best bet would
be to install a Japanese font on your PC and have a Japanese keyboard so
that you can see the right keycodes for data entry. Your message has
been encoded as UTF-8 which means the Japanese looks OK to me, but your
Anglicised 8bit version has lost a out in the conversion. You have to
work in a double byte character representation for Japanese. Unicode or
their native legacy DBCS representation.

"PA" wrote:

the chr() function is the same as the character function in Excel (not sure
of the name, I have french Excel). To get help on it, type chr in the VBA
editor and press F1.

Basicly, what it does is to return a character based on a number (0-255). So

Chr(74) & Chr(97) & Chr(112) & Chr(97) & Chr(110)

will return "Japan"

Explore a bit on it. Maybe you can return character from a different set of
letters (Japan).

Give me some news.

PA

"TKS_Mark" wrote:

What is the chr() function? Unfortunately, my programming experience it
limited to recording macros and trying to modify them slightly. If you give
me a hint, I can probably experiment and figure it out.

"PA" wrote:

Can you build your string in japan using the chr() function? Obviously, I
didn't try it on my computer;)

PA

"TKS_Mark" wrote:

I'm sharing an Excel 2007 file with coworkers in Japan. I use the indirect
function to build a formula based on several cells. The problem is, these
don't work on Japanese computers, so the formula results in #REF!

I was able to record a macro on a Japanese computer to translate from
English to Japanese:

Cells.Replace What:="[#Totals]", Replacement:="[#�WŒv]", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The WŒv looks like [#集計] on the Japanese computer. When I run the above

I suspect you have that backwards. #集計 on the Japanese computer looks
like WÅ’v on yours. The Unicode representations are along the lines of
# 集 計
23,00 c6,96, 08,8A

But after whatever transformation MS inflicts upon it your #WÅ’v is
encoded with an entirely different byte sequence.
# W Å’ v
23,00 8F,00 57,00 52,01 76,00

(there is a gap at least in the font I am using so 8F,00 has no visible
representation on an English PC)

There is a chance that a string encoded on a Western PC with
=Char(198)&Char(150)&Char(8)&Char(138)
or possibly with the odd even bytes swapped might be rendered correctly
on a Japanese PC. failing that try
=char(15672/256)+Char(MOD(15672,256))
(or byte swapped)

program, nothing happens. There are no warnings, etc. When I paste from the
Japanese macro into the Excel file, it also looks like WÅ’v.

How can I get the Japanese to be properly recognized?

Are you asking here how to install Japanese fonts on a Western PC or how
to input data that will look right in both a Japanese and Western
environment. The simplest way to do it is to have a page of the business
keywords with English in column A and Japanese translations in column B
(the latter will look crazy on a Western PC and some unfortunate
combinations may even be illegal strings). One extra level of
indirection should give you something that can work with English or
Japanese business keywords.

Good luck! You are going to need it.

Regards,
Martin Brown