VBA convert A1 reference to R1C1 reference

[neveu]

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

 

[Norie]

Can't you just use E51 etc in the code?

Where do you need use it in R1C1 ^* notation?

^* 51, 5 isn't really R1C1 notation, E51 in R1C1 notation would be R51C5.

 

[neveu]

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]

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]

Hi

You could use

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

 

[neveu]

10x a lot,
works great

 

[pgc01]

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:

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]

P.S.

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

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

 

[quantsegu]

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]

P.S.

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

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

Nicely done mate.

Biz