将表格列中的所有单元格设置为特定值

Question

我目前正在处理一个以表格形式排版、带有标题的数据集。我的任务是循环遍历一个特定列中的所有单元格并更改其内容。通过在 MSDN 上进行调研，我得到了以下 for 循环：

for i = 1 to NumRows
    Cells(i,23).Value = "PHEV"
next i

这将把第23列中的所有单元格更改为“PHEV”。但是，我没有自己构建使用的表格，因此无法保证我感兴趣的列将是第23列。

我想要实现类似以下的操作：

for i = 1 to NumRows
    Cells(i,[@[columnHeader]]).Value = "PHEV"
next i

当然，我知道那个语法是不正确的，但希望它能够足够地说明我的目标。

- detroitwilly

Answer 1

如果确实是从功能区中插入的ListObject表格（“插入”选项卡），那么您可以使用该表格的.DataBodyRange对象获取行数和列数。这会忽略标题行。

Sub TableTest()

Dim tbl As ListObject
Dim tRows As Long
Dim tCols As Long

Set tbl = ActiveSheet.ListObjects("Table1")  '## modify to your table name.

With tbl.DataBodyRange
    tRows = .Rows.Count
    tCols = .Columns.Count
End With

MsgBox tbl.Name & " contains " & tRows & " rows and " & tCols & " columns.", vbInformation

End Sub

如果您需要使用标题行，而不是使用tbl.DataBodyRange，请改用tbl.Range。

- David Zemens

---

tCols = .Columns.Count... 在我看来，这是最干净的解决方案。 - JoeFox

这段代码循环和引用表格单元格的适当方式是什么？ - user736893

@ScottBeeson 你可以遍历 tbl.DataBodyRange.Cells 集合，或者通过索引：tbl.DataBodyRange(1, 1) 等。 - David Zemens

那就像这样 For i = 1 To tbl.ListRows.Count tbl.Range("G" & i).value = "test"？ - user736893

我会执行 For i = 1 To tbl.ListRows.Count : tbl.ListRows(i, 7).Value = "test"（假设您想要表格中的第七列，无论该列是哪个字母列，否则请根据需要进行修改）。@ScottBeeson。否则，如果您在实施过程中遇到问题，请提出自己的新问题。干杯。 - David Zemens

Answer 2

假设您的表名为 'Table1'，需要的列名为 'Column'，您可以尝试这样做：

for i = 1 to Range("Table1").Rows.Count
   Range("Table1[Column]")(i)="PHEV"
next i

- George Lupu

---

谢谢！这个问题的解决方案非常简单而优雅，让我省去了使用其他更复杂的解决方案编码的麻烦。 - Angel

最本质的答案。应该被接受/获得最高票数（如果用户在以后继续注意它，预计它会随着时间而增加）。比其他网站上让我走弯路的无聊回答好多了。 - JeopardyTempest

想知道当你在处理一个非常大的数据表时，如果只检索一次列并从中进行操作，而不是反复使用范围，是否会更加高效。所以你需要额外的代码行...看起来像是在for循环之前添加myColumn = Range("Table1[Column]")（好像还需要类似dim myColumn as Variant这样的代码？它看起来像某种数组？或者我错过了更精确的数据类型？），然后在代码中使用myColumn(i) = "PHEV"。但在许多情况下这可能并不重要。 - JeopardyTempest

Answer 3

假设您的表名为“Table1”，列名为“Column1”，则：

For i = 1 To ListObjects("Table1").ListRows.Count
    ListObjects("Table1").ListColumns("Column1").DataBodyRange(i) = "PHEV"
Next i

- SnitkaSirk

Answer 4

您可以在分配之前搜索列：

Dim col_n as long
for i = 1 to NumCols
    if Cells(1, i).Value = "column header you are looking for" Then col_n = i
next

for i = 1 to NumRows
    Cells(i, col_n).Value = "PHEV"
next i

- LS_ᴅᴇᴠ

---

第三行，您使用了逗号而不是点来访问单元格的值。 - esylvestre

好答案。我希望Excel更加语义化...为什么我不能像在Python或JSON中的字典那样引用列，比如table ["column name"]？在进行其他表操作时，它有点做到了，例如引用同一行中的其他列值。 - william_grisaitis

@grisaitis 因为那太合理了。微软喜欢混淆。 - ATL_DEV

Answer 5

如果您知道标题名称，您可以根据它找到该列：

Option Explicit

Public Sub changeData()
    Application.ScreenUpdating = False ' faster for modifying values on sheet

    Dim header As String
    Dim numRows As Long
    Dim col As Long
    Dim c As Excel.Range

    header = "this one" ' header name to find

    Set c = ActiveSheet.Range("1:1").Find(header, LookIn:=xlValues)
    If Not c Is Nothing Then
        col = c.Column
    Else
        ' can't work with it
        Exit Sub
    End If

    numRows = 50 ' (whatever this is in your code)

    With ActiveSheet
        .Range(.Cells(2, col), .Cells(numRows, col)).Value = "PHEV"
    End With

    Application.ScreenUpdating = True ' reset
End Sub

- Joseph

---

如果您要将列中的所有单元格设置为一个值，则绝对没有必要循环遍历该范围来执行此操作。只需将范围设置为所需的值，就像@joseph4th在这里所做的那样。我还会声明一个工作表对象，这样您就不需要使用ActiveSheet。 - Jon Crowell

Answer 6

如果你知道表格中某一列的名称而不是其位置，那么可以通过循环遍历该列的单元格。假设该表格在工作簿的sheet1中：

Dim rngCol as Range
Dim cl as Range
Set rngCol = Sheet1.Range("TableName[ColumnName]")
For Each cl in rngCol
    cl.Value = "PHEV"
Next cl

上述代码将仅循环数据值，不包括标题行和总计行。在表格中不需要指定行数。

通过列名称使用此方法查找表格中任意列的位置：

Dim colNum as Long
colNum = Range("TableName[Column name to search for]").Column

这将返回表中列的数字位置。

- The Dude

Answer 7

我遇到了同样的问题，但是没有一个论坛可以帮助我。经过几分钟的思考，我想出了一个主意：

match(ColumnHeader,Table1[#Headers],0)

这将返回给您一个数字。

- Javier

Answer 8

你可以找到表格的最后一列，然后通过循环填充单元格。

Sub test()
    Dim lastCol As Long, i As Integer
    lastCol = Range("AZ1").End(xlToLeft).Column
        For i = 1 To lastCol
            Cells(1, i).Value = "PHEV"
        Next
End Sub

- Santosh

Answer 9

由于以上答案都没有帮助我解决问题，这里是我从每行中提取特定（命名的）列的解决方案。

我使用Excel VBA代码将表格中某些命名列（Yes、No、Maybe）的值转换为文本，来自名为myTable且位于标签页mySheet上的Excel表格：

Function Table2text()
    Dim NumRows, i As Integer
    Dim rngTab As Range
    Set rngTab = ThisWorkbook.Worksheets("mySheet").Range("myTable")
    ' For each row, convert the named columns into an enumeration
    For i = 1 To rngTab.Rows.Count
        Table2text= Table2text & "- Yes:"   & Range("myTable[Yes]")(i).Value & Chr(10)
        Table2text= Table2text & "- No: "   & Range("myTable[No]")(i).Value & Chr(10)
        Table2text= Table2text & "- Maybe: "& Range("myTable[Maybe]")(i).Value & Chr(10) & Chr(10)
    Next i
  ' Finalize return value 
  Table2text = Table2text & Chr(10)  
End Function

我们定义一个范围rngTab，在其中循环。诀窍是使用Range("myTable[col]")(i)来提取第col列中第i行的条目。

- B--rian

Answer 10

我修改了一下SnitkaSirk的答案。这是一种简单的方法来循环遍历listobject单元格，例如在一列中：

Dim cell As Range
For Each cell In ListObjects("NameOfTable").ListColumns("HeaderNameOfColumn").DataBodyRange
    Debug.Print x
Next

- pzelenin92