Column Property, Indirect reference?
 

I found this snip of code from Tom Ogilvy responding to a question from
OkieViking on how to convert the column# to the column-letter.

i= 27
colLetter = Left(cells(1,i).address(0,0),1-(cells(1,i).Column26))

I am looking for a concise way to do the converse.

i = "AA10"
colnum = ?formula? where the answer is 27
rownum = ?formula? where the answer is 10

I already have written a conversion routine using the ASC() function, some
string functions and loopiing, but I thought there might be a better (faster)
way to accomplish this.

I tried using the .column property, but that used the address of the cell
instead of its value.

in excel i would use =Column(indirect(Z10))
with Z10 containing the value "AA10" this would return 27, perhaps i could
use the external function call in VBA to do this, but I need to use the least
amout of resources to acomplish this task.

--
Regards,
John

Column Property, Indirect reference?
 

John,

Sub Test()
Dim lngCol As Long
Dim lngRow As Long

lngCol = Range("AA10").Column
lngRow = Range("AA10").Row
MsgBox lngCol & " " & lngRow

End Sub

Regards,
Jim Cone
San Francisco, USA


"John Keith" wrote in message
...
I found this snip of code from Tom Ogilvy responding to a question from
OkieViking on how to convert the column# to the column-letter.

i= 27
colLetter = Left(cells(1,i).address(0,0),1-(cells(1,i).Column26))

I am looking for a concise way to do the converse.

i = "AA10"
colnum = ?formula? where the answer is 27
rownum = ?formula? where the answer is 10

I already have written a conversion routine using the ASC() function, some
string functions and loopiing, but I thought there might be a better (faster)
way to accomplish this.

I tried using the .column property, but that used the address of the cell
instead of its value.

in excel i would use =Column(indirect(Z10))
with Z10 containing the value "AA10" this would return 27, perhaps i could
use the external function call in VBA to do this, but I need to use the least
amout of resources to acomplish this task.

--
Regards,
John