convert A1 to R1C1 in VBA
 

Is there a function that will do the following?:

convtA1toR1C1(strA1 As String) As String

So that with the following input I would get the following return?

convtA1toR1C1("F23:AL4257") = "R23C6:R4257C38"

Thanks,

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via http://www.officekb.com

convert A1 to R1C1 in VBA
 

I need this function because in spite of successfully setting the reference
to xlR1C1 in code, the currentregion.address returns in A1 style.

ragtopcaddy wrote:
Is there a function that will do the following?:

convtA1toR1C1(strA1 As String) As String

So that with the following input I would get the following return?

convtA1toR1C1("F23:AL4257") = "R23C6:R4257C38"

Thanks,


--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via http://www.officekb.com

convert A1 to R1C1 in VBA
 

not sure if this will help, just wanted to give it a try. it's mostly from the
help file

Function convtA1(strA1 As String) As String
MsgBox Application.ConvertFormula( _
Formula:=strA1, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1)

End Function


entered like this:
=convta1("SUM(B10:B20)")
--


Gary


"ragtopcaddy via OfficeKB.com" <u9289@uwe wrote in message
news:853bf2aeafc06@uwe...
I need this function because in spite of successfully setting the reference
to xlR1C1 in code, the currentregion.address returns in A1 style.

ragtopcaddy wrote:
Is there a function that will do the following?:

convtA1toR1C1(strA1 As String) As String

So that with the following input I would get the following return?

convtA1toR1C1("F23:AL4257") = "R23C6:R4257C38"

Thanks,


--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via http://www.officekb.com

convert A1 to R1C1 in VBA
 

You can get the R1C1 style address directly via the Address property's
optional arguments. For example, using ActiveCell for the Range object for
this example (use your own Range in place of it)...

Debug.Print ActiveCell.CurrentRegion.Address(ReferenceStyle:=x lR1C1)

Check out the help files for Address to see what other optional arguments
are available.

Rick


"ragtopcaddy via OfficeKB.com" <u9289@uwe wrote in message
news:853bf2aeafc06@uwe...
I need this function because in spite of successfully setting the reference
to xlR1C1 in code, the currentregion.address returns in A1 style.

ragtopcaddy wrote:
Is there a function that will do the following?:

convtA1toR1C1(strA1 As String) As String

So that with the following input I would get the following return?

convtA1toR1C1("F23:AL4257") = "R23C6:R4257C38"

Thanks,


--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via http://www.officekb.com

convert A1 to R1C1 in VBA
 

Thanks All.

I'll try your suggestions

Bill

Rick Rothstein (MVP - VB) wrote:
You can get the R1C1 style address directly via the Address property's
optional arguments. For example, using ActiveCell for the Range object for
this example (use your own Range in place of it)...

Debug.Print ActiveCell.CurrentRegion.Address(ReferenceStyle:=x lR1C1)

Check out the help files for Address to see what other optional arguments
are available.

Rick

I need this function because in spite of successfully setting the reference
to xlR1C1 in code, the currentregion.address returns in A1 style.
[quoted text clipped - 8 lines]

Thanks,

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via http://www.officekb.com