VBA-查找命名范围中第一个单元格的位置以引用电子表格的标题行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26802093/
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
VBA- Find location of first cell in named range to reference heading row of spreadsheet
提问by Jbone
What I have is part descriptions that I have split into separate columns. All columns have headings, but not all columns contain information for every part, ex: some might have size, material, and temp while another may have just size and temp. I have a function to concatenate them which ignores the blank spaces. I want to reference the header for the column before each cell I am concatenating.
我所拥有的是已拆分为单独列的部分描述。所有列都有标题，但并非所有列都包含每个部分的信息，例如：有些可能有尺寸、材料和温度，而另一些可能只有尺寸和温度。我有一个连接它们的函数，它忽略空格。我想在我连接的每个单元格之前引用列的标题。
Desired results:
预期结果：
When entering the following in B6 =ConcatenateRangeValve(G6:J6,",")
在 B6 中输入以下内容时 =ConcatenateRangeValve(G6:J6,",")
I want to see these results. [ITEM]Valve,[TYPE]Gate,[DIM]28IN
我想看到这些结果。[ITEM]阀门，[TYPE]Gate，[DIM]28IN
The items in [ ] are in Row 1:1 and I am having trouble getting my function to reference that row with the same column that I am in to pull the header. I think what it needs is to identify where the cell I am working in is in the whole spreadsheet. I attempted to do this by defining C, setting its value as the column number of the first cell in my range and then increasing it by 1 as it steps though the loop. I cannot get it to work. All the other pieces are fine. See below:
[ ] 中的项目在第 1:1 行中，我无法让我的函数引用与我拉标题所在的列相同的行。我认为它需要确定我正在工作的单元格在整个电子表格中的位置。我试图通过定义 C 来实现这一点，将其值设置为我范围内第一个单元格的列号，然后在循环时将其增加 1。我无法让它工作。所有其他部分都很好。见下文：
Function ConcatenateRangeValve(ByVal cell_range As Range, _
Optional ByVal seperator As String) As String
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long
Dim C As Long
cellArray = cell_range.Value
With Range("cell_range")
C = .Column
End With
For i = 1 To UBound(cellArray, 1)
For j = 1 To UBound(cellArray, 2)
If Len(cellArray(i, j)) <> 0 Then
newString = newString & (seperator & "[" & cells(1, C) & "]")
newString = newString & (cellArray(i, j))
End If
C = C + 1
Next
Next
If Len(newString) <> 0 Then
newString = Right$(newString, (Len(newString) - Len(seperator)))
End If
ConcatenateRangeValve = newString
End Function
Thanks in advance for any help you guys can offer.
在此先感谢你们提供的任何帮助。
采纳答案by Jbone
So, I am new to VBA, apparently I was using named range wrong. The solution I came up with was to replace
所以，我是 VBA 的新手，显然我使用的命名范围是错误的。我想出的解决方案是更换
With Range("cell_range")
C = .Column
End With
With
和
' cell_range(1).column returns the column index of the starting range from the passed variable
C = cell_range(1).Column
This returned the number corresponding to the column in the spreadsheet were the first cell of the array was located. This allowed me to reference the column heading using C.
这将返回对应于电子表格中位于数组的第一个单元格中的列的数字。这允许我使用 C 引用列标题。
回答by Mr. Mascaro
You can do it many different ways, but you can reference cells inside a range the same way as with a sheet. If your named range conatins the headers:
您可以通过多种不同的方式执行此操作，但您可以像使用工作表一样引用区域内的单元格。如果您的命名范围包含标题：
Range("NamedRange").Cells(1,1)
Range("NamedRange").Cells(1,1)
If your named range starts just below the headers:
如果您的命名范围从标题下方开始：
Range("NamedRange").Offset(-1,0)
Range("NamedRange").Offset(-1,0)
For all other cases:
对于所有其他情况：
Range(Cells(1,Range("NamedRange").Column))
Range(Cells(1,Range("NamedRange").Column))
