Set all cells in a table column to a specific value

I'm currently working on a data set which is formatted as a table, with headers. What I need to do is cycle through all cells in a specific column and change the contents. Through research on MSDN I came up with the following for loop

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

So this would change all the cells in column 23 to read "PHEV". However, I do not build the table I'm working with myself, so I can't guarantee that the column I'm interested in will be column 23.

I'd like to implement something similar to the following:

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

Of course, I know that that syntax is incorrect, but hopefully it sufficiently illustrates my goal.

Solution 1:

If this is in fact a ListObject table (Insert Table from the ribbon) then you can use the table's .DataBodyRange object to get the number of rows and columns. This ignores the header row.

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

If you need to use the header row, instead of using tbl.DataBodyRange just use tbl.Range.

Solution 2:

Assuming that your table is called 'Table1' and the column you need is 'Column' you can try this:

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

Solution 3:

Assuming your table is called "Table1" and your column is called "Column1" then:

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

Solution 4:

You can search column before assignments:

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

Solution 5:

If you know the header name, you can find the column based on that:

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