Hi All,

This is probably a very silly question, but unfortunately I could not find a
suitable solution so far. I would kindly request you to point me to some
link or provide some resource.

I have written an Excel Macro, which uses RefEdit to accept a Range from the
user. I need to create a Range object with this address. Everything works
fine, if the address is in xlA1 notation, or in xlR1C1 (English). But some
of my clients are using German version, which causes errors.

So far, (after searching the net for a long time), I have found the
following solutions.

' address is string with the address
' r is of type range

' I use this to convert from local to english.
r.FormulaR1C1Local= "=" & address
address=r.FormulaR1C1

and then use:
address=Application.ConvertFormula(address, xlR1C1, xlA1)

This give the desired solution in most cases but I get errors in some cases.

address FormulaR1C1 FormulaR1C1Local
=A1 =A1 =A1
=$A$1 1004 1004 Application-defined or Object-defined
error
=$A1 1004 1004 Application-defined or Object-defined
error
=A$1 1004 1004 Application-defined or Object-defined
error
=R1C1 ='R1C1' =R1C1
=$R1$C1 1004 1004 Application-defined or Object-defined error
=R1$C1 1004 1004 Application-defined or Object-defined error
=$R1C1 1004 1004 Application-defined or Object-defined error
=Z1S1 =R1C1 =Z1S1
=$Z1$S1 1004 1004 Application-defined or Object-defined error
=$Z1S1 1004 1004 Application-defined or Object-defined error
=Z1$S1 1004 1004 Application-defined or Object-defined error


result of Application.ConvertFormula

address result
A1 'A1'
$A$1 2015 Syntax Error
$A1 2015 Syntax Error
A$1 2015 Syntax Error
R1C1 $A$1
$R1$C1 2015 Syntax Error
R1$C1 2015 Syntax Error
$R1C1 2015 Syntax Error
Z1S1 Z1S1
$Z1$S1 2015 Syntax Error
$Z1S1 2015 Syntax Error
Z1$S1 2015 Syntax Error


Is there a way to convert from any of the above address to its equivalent
xlA1 ?

A1
$A$1
$A1
A$1
R1C1
$R1$C1
R1$C1
$R1C1
Z1S1
$Z1$S1
$Z1S1
Z1$S1



Thanks.