vba 如何获得一个单元格在一个范围内的位置？

How do I get a cell's position within a range?

提问by eirikdaude

How would I go about getting the relative position of a cell within a range? Finding the position of a cell in a worksheet is trivial, using the Row- and Column-properties, but I am unsure of how to do the same within a range.

我将如何获取某个范围内单元格的相对位置？使用Row- 和Column- 属性在工作表中查找单元格的位置很简单，但我不确定如何在范围内执行相同的操作。

I considered using the position of the top-left cell in the range I want to find the position of a cell in, and just deduct it (-1) from the position of the cell in the worksheet, but it gets a little bit cumbersome. Is there a more elegant way to go about this?

我考虑过使用我想在其中找到单元格位置的范围内左上角单元格的位置，然后从工作表中单元格的位置减去它（-1），但它会变得有点麻烦. 有没有更优雅的方法来解决这个问题？

My best attempt, including a test, so far is this:

我最好的尝试，包括测试，到目前为止是这样的：

Option Explicit

Sub test()
  Dim r As Range: Set r = Sheet1.Range("B2:E10")
  Dim c As Range: Set c = Sheet1.Range("C2")

  Debug.Print "Column in sheet: " & c.Column
  Debug.Print "Row in sheet: " & c.Row
  Debug.Print "Column in range: " & column_in_range(r, c)
  Debug.Print "Row in range: " & row_in_range(r, c)
End Sub

Function column_in_range(r As Range, c As Range) As Long
  column_in_range = c.Column - (r.Cells(1, 1).Column - 1)
End Function

Function row_in_range(r As Range, c As Range) As Long
  row_in_range = c.Row - (r.Cells(1, 1).Row - 1)
End Function

This gives the desired output:

这给出了所需的输出：

Column in sheet: 3
Row in sheet: 2
Column in range: 2
Row in range: 1

But I wonder if there are any native functions I can use instead?

但我想知道是否有任何我可以使用的本机函数？

采纳答案by Vasily

updated using variant provided bylori_m

使用提供的变体更新lori_m

But I wonder if there are any native functions ...

但我想知道是否有任何本机功能......

use this

用这个

Sub test()
    Dim r As Range, c As Range
    With Sheet1
        Set r = .[B2:E10]
        Set c = .[C2]
    End With
    If Not Intersect(r, c) Is Nothing Then
        Debug.Print "Column in sheet: " & c.Column
        Debug.Print "Row in sheet: " & c.Row
        Debug.Print "Column in range: " & Range(r(1), c).Columns.Count
        Debug.Print "Row in range: " & Range(r(1), c).Rows.Count
    End If
End Sub

output

输出

Column in sheet: 3
Row in sheet: 2
Column in range: 2
Row in range: 1

回答by Siddharth Rout

There is no native way to do it. I also do what you have mentioned in the code above. However I put some extra checks.

没有本地方法可以做到这一点。我也做了你在上面的代码中提到的。但是我做了一些额外的检查。

Sub test1()
    Dim r As Range: Set r = Sheet1.Range("B2:E10")
    Dim c As Range: Set c = Sheet2.Range("C2") '<~~ Changed Sheet1 to sheet2
    Dim rng As Range

    On Error Resume Next
    Set rng = Intersect(c, r)
    On Error GoTo 0

    '~~> Check if the range is in main range
    If Not rng Is Nothing Then
        '
        '~~> Rest of your code
        '
    Else
        MsgBox c.Address & " in " & c.Parent.Name & _
               " is not a part of " & _
               r.Address & " in " & r.Parent.Name
    End If
End Sub

回答by Kazimierz Jawor

In my opinion there is almost native way to check it but result is a string required some additional manipulation. All you need to use is a proper construction of .Address property(according to MSDN). Some examples:

在我看来，几乎有原生的方式来检查它，但结果是一个字符串需要一些额外的操作。您需要使用的只是一个正确的构造.Address property（根据MSDN）。一些例子：

Dim r As Range: Set r = Sheet1.Range("B2:E10")

Dim c As Range: Set c = Sheet1.Range("c2")
Debug.Print c.Address(False, False, xlR1C1, , r.Cells(0, 0))
        '>>result: R[1]C[2]
'-----------------------------------------------------

Set c = Sheet1.Range("e2")
Debug.Print c.Address(False, False, xlR1C1, , r.Cells(0, 0))
        '>>result: R[1]C[4]
'-----------------------------------------------------

Set c = Sheet1.Range("e5")
Debug.Print c.Address(False, False, xlR1C1, , r.Cells(0, 0))
        '>>result: R[4]C[4]
'-----------------------------------------------------

回答by R3uK

Take a look on MSDN to see more.

查看MSDN 以了解更多信息。

You can use something like :

你可以使用类似的东西：

MsgBox ActiveCell.Address(RowAbsolute:=True, _
                          ColumnAbsolute:=True, _
                          ReferenceStyle:=xlR1C1, _
                          External:=False, _
                          RelativeTo:=Range("B2"))

'Or shorter version :
MsgBox ActiveCell.Address(, , xlR1C1, False, Range("B2"))

But you'll have both information about row and column in the range, but not separately.

但是您将同时获得有关范围内行和列的信息，但不会分开。

So you'll still need to extract these values from the answer (look like : R18C20) in two functions, so almost the same issue...

所以你仍然需要R18C20在两个函数中从答案中提取这些值（看起来像 : ），所以几乎相同的问题......

回答by Rycket

I'm not totally sure if this is what you are after. But here it goes:

我不完全确定这是否是您所追求的。但它是这样的：

Sub ts2()

    Dim test As Range
    Set test = Range("B2:E10")

    Dim topcorner As Range
    Dim testcell As Range

    Set topcorner = Cells(test.Row, test.Column)
    Set testcell = Range("D7")
    rel_row = testcell.Row - topcorner.Row
    rel_col = testcell.Column - topcorner.Column

End Sub

By this, you will find the relative position. But maybe you were looking for some built in function ? If this was not the thing you were after, please edit your post...

这样，您将找到相对位置。但也许您正在寻找一些内置功能？如果这不是您所追求的，请编辑您的帖子...