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.
This question is related to
vba
excel
excel-2010
Since none of the above answers helped me with my problem, here my solution to extract a certain (named) column from each row.
I convert a table into text using the values of some named columns (Yes
, No
, Maybe
) within the named Excel table myTable
on the tab mySheet
using the following (Excel) VBA snippet:
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
We define a range rngTab
over which we loop. The trick is to use Range("myTable[col]")(i)
to extract the entry of column col
in row i
.
You can find the last column of table and then fill the cell by looping throught it.
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
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
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
I came across the same problem but no forum could help me, after some minutes I came out with an idea:
match(ColumnHeader,Table1[#Headers],0)
This will return you the number.
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
.
You can loop through the cells of any column in a table by knowing just its name and not its position. If the table is in sheet1 of the workbook:
Dim rngCol as Range
Dim cl as Range
Set rngCol = Sheet1.Range("TableName[ColumnName]")
For Each cl in rngCol
cl.Value = "PHEV"
Next cl
The code above will loop through the data values only, excluding the header row and the totals row. It is not necessary to specify the number of rows in the table.
Use this to find the location of any column in a table by its column name:
Dim colNum as Long
colNum = Range("TableName[Column name to search for]").Column
This returns the numeric position of a column in the table.
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
Source: Stackoverflow.com