Excel 2007 VBA 获取活动单元格相对于其在区域中的位置的行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18684716/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2007 VBA Get the row number of an active cell relative to its position in a range
提问by bafedm
I have been beating myself to death trying to figure this out, my google search page is filled with visited links as I try wording my searches in a hundred different ways with no success!
我一直在努力解决这个问题，我的谷歌搜索页面充满了访问过的链接，因为我尝试用一百种不同的方式来表达我的搜索，但没有成功！
I have a range, say A8:A17, and the active cell is A10. How do I get the row number of A10relative to the first cell in the range, A8(I would expect the result to be 3)? I want to use this value to reference another cell using Range(exRange).Cells(exRow, 1). The only way I can think of doing it is to loop through the range until the loop number is equal to the row of the active cell but there must be a cleaner way of doing it!
我有一个范围，比如说A8:A17，活动单元格是A10。如何获得A10相对于范围内第一个单元格的行号A8（我希望结果是3）？我想使用这个值来引用另一个单元格Range(exRange).Cells(exRow, 1)。我能想到的唯一方法是循环遍历范围，直到循环数等于活动单元格的行，但必须有一种更简洁的方法来做到这一点！
回答by Gary's Student
Use the RowProperty:
使用Row属性：
Sub bafedm()
Dim Tabl As Range, r As Range
Set Tabl = Range("A8:A17")
Set r = Range("A10")
MsgBox r.Row - Tabl.Row + 1
End Sub
For reference, here is some code for other properties and dimensions of a typical rectangular range:
作为参考，以下是典型矩形范围的其他属性和尺寸的一些代码：
Sub range_reporter()
Dim r As Range
Dim nLastRow As Long, nLastColumn As Long
Dim FirstRow As Long, nFirstColumn As Long
ActiveSheet.UsedRange
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)
nLastColumn = r.Columns.Count + r.Column - 1
MsgBox ("last column " & nLastColumn)
nFirstRow = r.Row
MsgBox ("first row " & nFirstRow)
nFirstColumn = r.Column
MsgBox ("first column " & nFirstColumn)
numrow = r.Rows.Count
MsgBox ("number of rows " & numrow)
numcol = r.Columns.Count
MsgBox ("number of columns " & numcol)
End Sub
回答by Alex P
How about:
怎么样：
=MATCH(A10, $A$:$A18$, 0) //returns 3
