vba 列名、定义名的行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17417301/
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
Column Name, Row Number of a Defined Name
提问by Reccax
I want to know the command to find the Column name of a Defined name and a row number of a defined name in vba. So if B3 is named Candy, i search for Candy and return column B. I google for an hour and couldn't find an answer.
我想知道在vba中查找已定义名称的列名和已定义名称的行号的命令。因此，如果 B3 被命名为 Candy，我会搜索 Candy 并返回 B 列。我用谷歌搜索了一个小时，但没有找到答案。
I'm going to use those column name and row number to get data from the intersection and extract it into another worksheet.
我将使用这些列名和行号从交集获取数据并将其提取到另一个工作表中。
采纳答案by Doug Glancy
You can pass a worksheet and a named range that's in it to this sub and it will print the range's address and first Columnand first Rowto the Immediate Window. If the range is more than one cell, it will also print the last column and row:
你可以通过一个工作表，这就是它这个子命名范围，将打印范围的地址和第一Column和第一Row到立即窗口。如果范围不止一个单元格，它还会打印最后一列和最后一行：
Sub PrintNameRowAndCol(ws As Excel.Worksheet, strName As String)
Dim rng As Excel.Range
Dim SplitAddress As Variant
On Error Resume Next
Set rng = ws.Range(strName)
On Error GoTo 0
If Not rng Is Nothing Then
Debug.Print rng.Address
SplitAddress = Split(rng.Address, "$")
Debug.Print "Column: "; SplitAddress(1)
Debug.Print "Row: "; SplitAddress(2)
If rng.Cells.Count > 1 Then
Debug.Print "Last Column: "; SplitAddress(3)
Debug.Print "Last Row: "; SplitAddress(4)
End If
End If
End Sub
If there is no range with that name, nothing happens.
如果没有具有该名称的范围，则不会发生任何事情。
The routine uses the Splitfunction which breaks a string into an array based on a delimiter, "$" in this case. Since the Range's Addressproperty returns a string in the absolute reference format, i.e., $Column$Row, the Split function always returns an array with two elements, the column and row, for a single-cell range. For a multiple cell range it returns four elements.
该例程使用Split基于分隔符“$”将字符串分解为数组的函数。由于 Range 的Address属性以绝对引用格式返回一个字符串，即 $Column$Row，因此 Split 函数始终返回一个包含两个元素的数组，即列和行，用于单个单元格范围。对于多单元格范围，它返回四个元素。
Call it like this:
像这样调用它：
Sub test()
PrintNameRowAndCol ActiveSheet, "Candy"
End Sub
EDIT: In answer to your specific question in the comments, you don't really need to figure the column letters and row numbers of the two ranges. In order to get the contents of the intersection of the column of one range and the row of another, and then assign it to a 3rd range, do something like this:
编辑：在回答您在评论中的具体问题时，您实际上并不需要计算两个范围的列字母和行号。为了获得一个范围的列和另一个范围的行的交集的内容，然后将其分配给第三个范围，请执行以下操作：
With ActiveSheet
.Range("D12") = .Cells(.Range("Cost").Row, .Range("Candy").Column)
End With
