Find column with specific name and fill the field with specific text to the last row

Question

I need to find column named "Current" (preferred) or last column in general in the table named "YTD_input1" and fill all rows with text "Current" to the last row of the table.

My suggested code is below but does not work.

 Dim pasteSheet As Worksheet: Set pasteSheet = wb.Worksheets("DPM input YTD_working")
 Dim LastRow As Long, Nxtrw As Long, lCol As Long

 LastRow = copySheet.Cells(Rows.Count, 1).End(xlUp).Row

 lCol = pasteSheet.Cells(1, Columns.Count).End(xlToLeft).Select
 
 pasteSheet.Range(lCol & LastRow).Value = "Current"

Can you please give me some advice how to build the code to be working?

"does not work" is not very descriptive. Can you let us know what happens, exactly? — cybernetic.nomad, Feb 07 '22 at 20:06
So find column the header "current" in Row1 and fill the entire data table column below that with "Current", and if there's no existing header with that name then fill the value under the last header? — Tim Williams, Feb 07 '22 at 20:07
.... if there's no existing header with that name then do nothing — benjin, Feb 07 '22 at 20:17
code throws bug in last line of code pasteSheet.Range(lCol & LastRow).Value = "Current" where I am not able to define range of last column — benjin, Feb 07 '22 at 20:18

VBasic2008 · Answer 1 · 2022-02-07T20:48:48.803

Fill an Excel Table Column

Option Explicit

Sub FillTableColumn()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("DPM input YTD_working")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("YTD_input1")
    
    Dim lcl As ListColumn
    On Error Resume Next
        Set lcl = tbl.ListColumns("Current")
    On Error GoTo 0
    
    ' Reference the column data range (no header).
    Dim cdrg As Range
    
    ' Either...
    If Not lcl Is Nothing Then ' column 'Current' found
        Set cdrg = lcl.DataBodyRange
        ' Fill
        cdrg.Value = "Current"
    'Else ' column 'Current' not found - do nothing
    End If
    
'    ' ... or e.g.:
'    If lcl Is Nothing Then ' column 'Current' found
'        Set cdrg = lcl.DataBodyRange
'    Else ' column 'Current' not found - reference the last columnn
'        Set cdrg = tbl.DataBodyRange.Columns(tbl.DataBodyRange.Columns.Count)
'    End If
'
'    ' Fill
'    cdrg.Value = "Current"

End Sub

score 1 · Accepted Answer · answered Feb 07 '22 at 21:08

Using Match()

Dim m, ws as worksheet, lr as long

set ws = ActiveSheet                              'or whatever
m = Application.Match("Current", ws.rows(1), 0)   'find the column

If Not IsError(m) Then 'got a match?
    lr = ws.Cells(rows.count, 1).End(xlUp).Row    'last row Col A
    ws.Range(ws.Cells(1, m).Offset(1), ws.Cells(lr, m)).Value = "Current"
End If

Find column with specific name and fill the field with specific text to the last row

2 Answers2

Fill an Excel Table Column