Formula to Convert Text Range Into R1C1 Style ?

[Ken1000]

Hi. I typically do not use the R1C1 reference style when composing
formulas. But I am wondering if you are aware of any excel formula or,
if necessary, a VBA solution, that would "convert" a specified range into
R1C1 format ?

Example. Cell F1 has text entry "D31:D50". I'd like this same text range
to show in cell F2, in R1C1 style. That is, with the proper formula entered
into cell F2, (or by means of some VBA code), the visible results would yield
"R31C4:R50C4" in F2.

Thank you for any help along these lines.

Ken

 

[Tazguy37]

You can switch between R1C1 style and A1 style in Tools, Options, General.

Also, you can view the formulas in your worksheet instead of the values by pressing Control-`

Hope that helps!

 

[acw]

Hi

the following UDF should give you what you are chasing.

Function myfunc(ra As Range)
 myfunc = Range(ra.Value).Address(ReferenceStyle:=xlR1C1)
End Function

Using your data in F1 the formula
=myfunc(F1)
in F2 gave R31C4:R50C4

HTH

Tony

 

[Ken1000]

Tazguy, thank you. I already knew about that capability.
Thank you, acw, for the helpful code.

It's nice to have such high caliber help.

Ken

 

[mahhdy]

Hello,

Just for the future refrence. Here is the general way as I found.

Application.ConvertFormula("your Formula",xlA1,xlr1c1)

First one is the formula type your formula have and the second one, the formula type you want.
if your formula is in a cell, use

yourcell.fromula

Regards,
M