Excel VBA将单元格名称转换为它的坐标(Excel VBA Convert Cell Name to It's Coordinates)
假设我有一个表示单元格的字符串: A2 。 如何将其转换为坐标: (2, 1) ?
Let's say I have this string which represents a cell: A2. What should I do to covert it to coordinates: (2, 1) ?
最满意答案
没有VBA
假设,单元格C2包含字符串"A2" 。
然后
=INDIRECT(C2)返回对A2引用 =ROW(INDIRECT(C2))返回行号 - 2 =COLUMN(INDIRECT(C2))返回列号 - 1 ="(" & ROW(INDIRECT(C2)) & "; " & COLUMN(INDIRECT(C2)) & ")"以格式(x; y) - (2; 1)返回坐标UPD:
如果您使用的是UDF,请将参数类型从String更改为Range :
Function GetData(Cell As Range) MsgBox "My row is " & Cell.Row MsgBox "My column is " & Cell.Column End Function如果从这样的工作表中调用此UDF: =GetData(A2) ,将弹出msg框:
“我的排是2” “我的专栏是1”Without VBA
Suppose, cell C2 contains string "A2".
Then
=INDIRECT(C2) returns reference to A2 =ROW(INDIRECT(C2)) returns row number - 2 =COLUMN(INDIRECT(C2)) returns column number - 1 ="(" & ROW(INDIRECT(C2)) & "; " & COLUMN(INDIRECT(C2)) & ")" returns coordinates in format (x; y) - (2; 1)UPD:
If you're using UDF, change your parameter type from String to Range:
Function GetData(Cell As Range) MsgBox "My row is " & Cell.Row MsgBox "My column is " & Cell.Column End Functionif you call this UDF from worksheet like this: =GetData(A2), msg box would pop-up:
"My row is 2" "My column is 1"更多推荐
发布评论