如何遍历表并通过其列标题访问行项目? [英] How to loop though a table and access row items by their column header?

问题描述

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

I have the following macro which needs to loop though an Excel-2007 table. The table has several columns and I am currently finding the correct column position using the Index property columns.

使用索引是我可以找到的正确索引到fName对象的唯一方法.我希望更好的选择是使用列名/标题"访问特定的列.我该怎么做，甚至可以做到?

Using the index is the only way I could find to correctly index into the fName object. The better option I am hoping for is to access specific columns using the Column Name/Header. How can I do this and can this be even done?

此外，总的来说，有没有更好的方法来构造此循环?

Furthermore, in general, is there a better way to construct this loop?

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

推荐答案

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

If you want to find a specific value in a column heading, you can use the find method. The find method returns a range, which you can then use as a reference to perform the rest of the operation. There are a lot of optional parameter to the find method, read up on it in the help docs if you need to tweak it more.

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)

这篇关于如何遍历表并通过其列标题访问行项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！