需要使用 VBA 在 Excel 中返回表中的行值。可以是任何表格中不能使用活动单元格的任何表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12653713/
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
Need to return the row value in a table in Excel using VBA. Could be any table any where on sheet cannot use activecell
提问by user1708386
Using VBA in Excel:
在 Excel 中使用 VBA：
I have many sheets that have multiple tables. The only thing these tables have in common is a ProductID column and LotNumber column. The data in the Products ID column needs to be validated and or manipulated. I need a way to get the Table Row Index number of the current TABLE of the currently selected cell. I can not use ActiveCell reference because the table rows do not match the sheet rows. I can not use find the last used cell in a column because there are other tables in the way.
我有很多有多个表格的工作表。这些表唯一的共同点是 ProductID 列和 LotNumber 列。产品 ID 列中的数据需要验证和/或操作。我需要一种方法来获取当前选定单元格的当前表格的表格行索引号。我不能使用 ActiveCell 引用，因为表格行与工作表行不匹配。我无法使用 find 列中最后使用的单元格，因为还有其他表格。
I need to be able to use this sub in many different workbooks.
我需要能够在许多不同的工作簿中使用这个子程序。
Here is the code I have so far.
这是我到目前为止的代码。
Const tblUNLABELED As String = "tblUnLabel"
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
StopAppEvents
Dim ws As Worksheet
Set ws = ActiveSheet
Dim intRow As Integer
Dim strTest As String
If Not Intersect(ActiveCell, _
ActiveSheet.Range(tblUNLABELED & "[LotNumber]")) Is Nothing Then
'go to the product entered in this table row and Ucase(ProductID)
strTest = ws.ListObjects(tblUNLABELED).Range(intRow, "[ProductID]")
ws.ListObjects(tblUNLABELED).Range(intRow, "[ProductID]") = UCase(strTest)
End If
RestartAppEvents
End Sub
Any help would be welcomed.
欢迎任何帮助。
回答by Doug Glancy
This uses the Range's ListObject Property to tell you the column name and row number of the table that the ActiveCell is in. If the ActiveCell isn't in a table you get a message saying so. A row number of 0 means the ActiveCell is in the table's header row:
这使用 Range 的 ListObject 属性告诉您 ActiveCell 所在表的列名和行号。如果 ActiveCell 不在表中，您会收到一条消息，说明如此。行号 0 表示 ActiveCell 位于表的标题行中：
Sub PrintActiveCellTableData()
Dim cell As Excel.Range
Dim lo As Excel.ListObject
Dim loCol As Excel.ListColumn
Dim loRowNum As Long
Set cell = ActiveCell
If cell.ListObject Is Nothing Then
Debug.Print "No ActiveCell ListObject"
Exit Sub
End If
Set lo = cell.ListObject
With lo
Set loCol = .ListColumns(cell.Column - (.HeaderRowRange.Cells(1).Column) + 1)
loRowNum = cell.Row - .HeaderRowRange.Row
End With
Debug.Print "Table Name: "; lo.Name; " ListColumn: "; loCol.Name; " Row:"; loRowNum
End Sub
回答by Charles Williams
If the selected cell is within one of your tables and your tables are bounded with empty cells then ActiveCell.CurrentRegion should give you a range that encompasses the table, and you can work from there.
如果所选单元格在您的一个表格内，并且您的表格以空单元格为界，则 ActiveCell.CurrentRegion 应该为您提供一个包含表格的范围，您可以从那里开始工作。
ActiveCell.Row - ActiveCell.CurrentRegion.Row + 1
ActiveCell.Row - ActiveCell.CurrentRegion.Row + 1
gives you the (1-based) row number of the active cell within the current table
为您提供当前表格中活动单元格的（基于 1 的）行号
