如何遍历一个表并通过它们的列标题访问行项目？

Question

我有下面的宏，需要循环通过Excel-2007表。该表有几列，我目前正在使用Index属性列查找正确的列位置。

使用索引是我能找到的唯一正确索引到fName对象的方法。我希望的更好的选择是使用列名/标题来访问特定的列。我如何才能做到这一点，甚至可以做到呢？

此外，一般来说，有没有更好的方法来构建这个循环？

Worksheets("Lists").Select

Dim filesToImport As ListObject 
Dim fName As Object
Dim fileNameWithDate As String

Dim newFileColIndex As Integer
Dim newSheetColIndex As Integer
Set filesToImport = ActiveSheet.ListObjects("tblSourceFiles")

newFileColIndex = filesToImport.ListColumns("New File Name").Index // <- Can this be different?

For Each fName In filesToImport.ListRows // Is there a better way?
    If InStr(fName.Range(1, col), "DATE") <> 0 Then
        // Need to change the ffg line to access by column name
        fileNameWithDate = Replace(fName.Range(1, newFileColIndex).value, "DATE", _
                                  Format(ThisWorkbook.names("ValDate").RefersToRange, "yyyymmdd"))
        wbName = OpenCSVFIle(fPath & fileNameWithDate)
        CopyData sourceFile:=CStr(fileNameWithDate), destFile:=destFile, destSheet:="temp"
    End If

Next fName2

Answer 1

如果要查找列标题中的特定值，可以使用find方法。find方法返回一个范围，然后您可以将其用作执行其余操作的引用。find方法有很多可选参数，如果需要进一步调整，请阅读帮助文档中的相关信息。

Dim cellsToSearch As Range
Dim foundColumn As Range
Dim searchValue As String

Set cellsToSearch = Sheet1.Range("A1:D1")  ' Set your cells to be examined here
searchValue = "Whatever you're looking for goes here"

Set foundColumn = cellsToSearch.Find(What:=searchValue)

Answer 2

前言

我通过谷歌找到了这个，我发现它还不够。因此，我将填写更多信息，解释发生了什么，并对代码进行一些优化。

解释

应该带给你的明显答案是：

是的，这是可以做到的。事实上，这比你想象的要简单。

我注意到是你做的

newFileColIndex = filesToImport.ListColumns("New File Name").Index

它为您提供了标题"New File Name“的索引。

然后，当您决定检查列时，您忘记了索引实际上也是相对列位置。

因此，您应该像以前一样做同样的事情，而不是列编号

InStr(fName.Range(1, filesToImport.ListColumns("Column Name")), "DATE")

让我们更深入一点，不仅用文字解释，还要用图片解释

在上图中，第一行显示了绝对列索引，

其中A1的列索引为1，B1的列索引为2，依此类推。

ListObject的头部有它们自己的相对索引，在本例中，Column1的列索引为1，Column2的列索引为2，依此类推。这允许我们在引用带有数字或名称的列时利用ListRow.Range属性。

为了更好地演示，这里有一段代码，它打印上一张图片中"Column1“的相对和绝对列索引。

Public Sub Example()
    Dim wsCurrent As Worksheet, _
        loTable1 As ListObject, _
        lcColumns As ListColumns

    Set wsCurrent = ActiveSheet
    Set loTable1 = wsCurrent.ListObjects("Table1")
    Set lcColumns = loTable1.ListColumns

    Debug.Print lcColumns("Column1").Index        'Relative. Prints 1
    Debug.Print lcColumns("Column1").Range.Column 'Absolute. Prints 3
End Sub

由于ListRow.Range指的是范围，因此这是一个相对论问题，因为该范围在ListObject内。

​

例如，要在ListRow的每次迭代中引用Column2，您可以这样做

Public Sub Example()
    Dim wsCurrent As Worksheet, _
        loTable1 As ListObject, _
        lcColumns As ListColumns, _
        lrCurrent As ListRow

    Set wsCurrent = ActiveSheet
    Set loTable1 = wsCurrent.ListObjects("Table1")
    Set lcColumns = loTable1.ListColumns

    For i = 1 To loTable1.ListRows.Count
        Set lrCurrent = loTable1.ListRows(i)

        'Using position: Range(1, 2)
        Debug.Print lrCurrent.Range(1, 2)
        'Using header name: Range(1, 2)
        Debug.Print lrCurrent.Range(1, lcColumns("Column2").Index)
        'Using global range column values: Range(1, (4-2))
        Debug.Print lrCurrent.Range(1, (lcColumns("Column2").Range.Column - loTable1.Range.Column))
        'Using pure global range values: Range(5,4)
        Debug.Print wsCurrent.Cells(lrCurrent.Range.Row, lcColumns("Column2").Range.Column)
    Next i
End If

优化的代码

正如承诺的那样，以下是优化后的代码。

Public Sub Code()
    Dim wsCurrentSheet As Worksheet, _
        loSourceFiles As ListObject, _
        lcColumns As ListColumns, _
        lrCurrent As ListRow, _
        strFileNameDate As String

    Set wsCurrentSheet = Worksheets("Lists")
    Set loSourceFiles = wsCurrentSheet.ListObjects("tblSourceFiles")
    Set lcColumns = loSourceFiles.ListColumns

    For i = 1 To loSourceFiles.ListRows.Count
        Set lrCurrent = loSourceFiles.ListRows(i)

        If InStr(lrCurrent.Range(1, lcColumns("Column Name").Index), "DATE") <> 0 Then
            strSrc = lrCurrent.Range(1, lcColumns("New File Name").Index).value
            strReplace = Format(ThisWorkbook.Names("ValDate").RefersToRange, "yyyymmdd")

            strFileNameDate = Replace(strSrc, "DATE", strReplace)
            wbName = OpenCSVFile("Path" & strFileNameDate)
            CopyData sourceFile:=CStr(strFileNameDate), _
                     destFile:="file", _
                     destSheet:="temp"
        End If
    Next i
End Sub

参考文献

亲身经历。

MSDN

• ListObject
• ListColumns
• ListRows

Answer 3

这是一个很方便的函数：

Function rowCell(row As ListRow, col As String) As Range
    Set rowCell = Intersect(row.Range, row.Parent.ListColumns(col).Range)
End Function