查找具有特定名称的列，并将具有特定文本的字段填充到最后一行

Question

我需要找到名为"当前"；(优选(或表中的最后一列；YTD_ input1"；并用文本"填充所有行；当前"；到表的最后一行。

我建议的代码在下面，但不起作用。

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"

你能给我一些如何构建代码以使其工作的建议吗？

Answer 1

使用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

Answer 2

填充Excel表格列

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