Set all cells in a table column to a specific value

excel, excel-2010, vba

Solution

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

Problem

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.

Original source