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?

Answer 1

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