VBA convert A1 reference to R1C1 reference

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
hello forum,

i have a list of references that i would like to convert to R1C1 reference.
for example for E51 to have 51,5 so then i could use these in my VBA code.
any ideas?

here is the entire list to be converted
E51
M51
S51
E53
S53
E55
E59
L59
AA59
E61
T61
E67
L67
T67
E69
Z69
E71
O71
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Can't you just use E51 etc in the code?

Where do you need use it in R1C1 <sup>*</sup> notation?

<sup>*</sup> 51, 5 isn't really R1C1 notation, E51 in R1C1 notation would be R51C5.
 

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
i would, but there were some situation when someone used the R1C1 notation and the code cracked.

so it was decided to use the the notation with cells(row_number,column_number) in the VBA code rather than the range("A1").

so, i would need to convert the list that i have into the corresponding one for the object method
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
I still don't see why you need to do that, Range("E51") is the same as Cells(51, 5).

They both refer to the same range object, have the same properties etc.

Can you post the code you need to do this with?

PS You say the R1C1 notation cracked the code but you want to use R1C1 notation.
 

quantsegu

New Member
Joined
Nov 15, 2011
Messages
2

ADVERTISEMENT

Hi

You could use
Code:
Sub D(ByVal a As String)
Dim col As Integer
Dim i As Integer

If (Len(a) > 1) Then
    For i = 1 To Len(a) - 1
        col = ConverttoInt(Mid(a, i, 1)) * 26
    Next i
    col = col + ConverttoInt(Right(a, 1))
    MsgBox col
Else
    MsgBox ConverttoInt(a)
End If

End Sub

Function ConverttoInt(ByVal chr As String) As Long
    ConverttoInt = Asc(UCase(chr)) - 64
End Function

Regards
Segu
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881

ADVERTISEMENT

hello forum,

i have a list of references that i would like to convert to R1C1 reference.
for example for E51 to have 51,5 so then i could use these in my VBA code.
any ideas?

here is the entire list to be converted
E51
...

Hi

"E51" cannot be converted to R1C1 style without a base cell reference that it is relative to. I guess you mean "$E$51"?

In that case try:

Code:
MsgBox Application.ConvertFormula(Formula:="$E$51", FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)

... but there were some situation when someone used the R1C1 notation ...

I understand him completely, I'm a big fan of the R1C1 style. :)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
P.S.

If you don't need to convert a formula, just the cell address, you can also use:

Code:
MsgBox Range("$E$51").Address(ReferenceStyle:=xlR1C1)
 

quantsegu

New Member
Joined
Nov 15, 2011
Messages
2
Hi,

ConvertFormula, is the one you are looking for, i was just converting given column character to integer. Which might not be what you are exactly looking for.

Thanks pgc01, misread post.

Rgds
Segu
 

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,677
P.S.

If you don't need to convert a formula, just the cell address, you can also use:

Code:
MsgBox Range("$E$51").Address(ReferenceStyle:=xlR1C1)

Nicely done mate.

Biz
 

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top