时间戳

首页 > 解决方案 > 引用表头

excel - 引用表头

问题描述

我正在做一个工程项目，并有代码在列中搜索字符串并将该行单元格中的信息检索到用户窗体上的文本框中。现在它引用列号，这工作正常。

我不够精明，无法将其引用为列标题而不是列号。尝试了几种离开谷歌的方法，但没有任何乐趣。我可以这样做，因为我有一个用户表单，用于多张表上的多个表，其中列可能不同。

有人能帮助我吗。当前使用的代码（用于列号）如下。作为一个让我开始的例子，第一个表名是“kiln1tbl”。

Dim srchrecord As String
srchrecord = Trim(dateTextBox.Value & ", " & timeTextBox.Value)
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
If ActiveSheet.Cells(i, 1).Value = srchrecord Then
    flowTextBox.Value = ActiveSheet.Cells(i, 4).Value
    densityTextBox.Value = ActiveSheet.Cells(i, 5).Value
end if
Next

我有一个类似命令的例子（工作正常）是

Dim tbl As ListObject
Set tbl = Worksheets("Kiln 1").ListObjects("kiln1tbl")
Intersect(.Range.Rows(1), tbl.ListColumns("material").Range.Columns(1)).Value

我尝试将其调整为我需要的新代码，但它不喜欢它。提前致谢

标签： excelvba

解决方案

表格研究

  • 这说明了如何引用表中的各种范围（列）及其元素（行）。
  • 调整工作簿、工作表、表格和列标题。
Option Explicit

Sub TableStudy()
    
    ' Create a reference to the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet
    ' Create a reference to the first table.
    Dim tbl As ListObject: Set tbl = ws.ListObjects(1)
    ' Get the indexes of the columns.
    Dim cFlow As Long: cFlow = tbl.ListColumns("Flow").Index
    Dim cDensity As Long: cDensity = tbl.ListColumns("Density").Index
    ' Create a reference to the (table data body) range.
    Dim rg As Range: Set rg = tbl.DataBodyRange
    ' Write the values of the range to an array.
    Dim Data As Variant: Data = rg.Value
    
    Debug.Print "Headers & Data" & vbLf
    
    ' Print the headers
    Debug.Print tbl.HeaderRowRange(1), tbl.HeaderRowRange(cFlow), _
        tbl.HeaderRowRange(cDensity)
    
    ' Print the data.
    Dim r As Long
    ' Loop through the rows of the array...
    For r = 1 To UBound(Data, 1)
        If Data(r, 1) > 0 Then
            Debug.Print Data(r, 1), Data(r, cFlow), Data(r, cDensity)
        End If
    Next r
    
    ' Print some addresses.
    Debug.Print vbLf & "How to reference the various ranges of the columns"
    Debug.Print "Range Addresses"
    Debug.Print tbl.ListColumns(1).Range.Address, _
        tbl.ListColumns(cFlow).Range.Address, _
        tbl.ListColumns(cDensity).Range.Address
    Debug.Print "Header Row Range Addresses"
    Debug.Print tbl.HeaderRowRange(1).Address, _
        tbl.HeaderRowRange(cFlow).Address, _
        tbl.HeaderRowRange(cDensity).Address
    Debug.Print "Data Body Range Addresses"
    Debug.Print tbl.ListColumns(1).DataBodyRange.Address, _
        tbl.ListColumns(cFlow).DataBodyRange.Address, _
        tbl.ListColumns(cDensity).DataBodyRange.Address
    
    Debug.Print vbLf & "How to reference the various ranges of the table"
    Debug.Print "Range", "HeaderRow", "DataBody"
    Debug.Print tbl.Range.Address, tbl.HeaderRowRange.Address, _
        tbl.DataBodyRange.Address
    
End Sub

将前面的内容应用于您的第一个示例可能会导致如下结果：

Dim srchrecord As String
srchrecord = Trim(dateTextBox.Value & ", " & timeTextBox.Value)

Dim ws As Worksheet: Set ws = ActiveSheet
Dim tbl As ListObject: Set tbl = ws.ListObjects(1)

Dim cFlow As Long: cFlow = tbl.ListColumns("Flow").Index
Dim cDensity As Long: cDensity = tbl.ListColumns("Density").Index

Dim rg As Range: Set rg = tbl.DataBodyRange
Dim Data As Variant: Data = rg.Value

Dim r As Long
For r = 1 To UBound(Data, 1)
    If Data(r, 1) = srchrecord Then
        flowTextBox.Value = Data(r, cFlow)
        densityTextBox.Value = Data(r, cDensity)
    End If
Next r

推荐阅读