Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
object reference does not support this property or method | Excel Discussion (Misc queries) | |||
Making column reference in INDIRECT non-static | Excel Worksheet Functions | |||
Indirect reference (again?) | Excel Worksheet Functions | |||
Excel should have a absolute reference cell property. | Excel Discussion (Misc queries) | |||
Cannot use .IsBroken property of a Reference object - error 48 | Excel Programming |