Extracting the column letter and row number

[Todd Huttenstine]

Hey

What code will extract only the column letter and then row
number from the below sample data?

Sheet1!$K$8
The desired outcome is a variable that will store K and
another variable that will store 8

Sheet1!$AC$108
The desired outcome is a variable that will store AC and
another variable that will store 108

Sheet1!$F$92
The desired outcome is a variable that will store F and
another variable that will store 92


Thanks in advance.
Todd Huttenstine

 

[Frank Kabel]

Hi Todd
why do you want the column letter. It would be easier to use the column
index?. But anyway:
1. Row number:
=ROW(Sheet1!$K$8)

2. Column letter
=SUBSTITUTE(ADDRESS(1,COLUMN(AC1),4),1,"")

 

[Todd Huttenstine]

Hey Frank thanks for the response, however the value
Sheet1!$AC$108 is stored in a variable in a userform
module. How would I extract from this?


Thanks
Todd Huttenstine

 

[Frank Kabel]

Hi
one way: use InStr and Mid to extract the part between the two $ signs

 

[Jake Marx]

Hi Todd,

Hey Frank thanks for the response, however the value
Sheet1!$AC$108 is stored in a variable in a userform
module. How would I extract from this?

These functions may work for you:

Public Function glGetRowNumber(rsAddress As String) As Long
On Error Resume Next
glGetRowNumber = Range(rsAddress).Row
On Error GoTo 0
End Function

Public Function gsGetColLetter(rsAddress As String) As String
On Error Resume Next
gsGetColLetter = Split(Range(rsAddress _
).Address(True, False), "$")(0)
On Error GoTo 0
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]