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

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?

CodePudding user response：

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

CodePudding user response：

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