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

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

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?

Solution

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