vba - 在不使用单元格引用的情况下填充具有相同值的列

Question

我需要在名为“Funded”的列下填充值，直到数据集末尾的最后一行。该列可以位于工作表上的任何位置，因此我不能使用单元格引用。

  Dim shtA As Worksheet
  Dim LastCol As Long
  Dim lastRow As Long
  Set shtA = ActiveWorkbook.Sheets("Financial")
  LastCol = shtA.Cells(1, Columns.Count).End(xlToLeft).Column
  lastRow = shtA.Cells(Rows.Count, 1).End(xlUp).Row



 'Add column header for Funded 
 Dim fund As Long
 On Error Resume Next
 fund = Cells.Find(What:="", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        ActiveCell.FormulaR1C1 = "Funded"
 On Error GoTo 0

'Loop through Funded to get column number
  Dim fund As Long
  Dim a As Long
  For a = 1 To LastCol
    If LCase(shtA.Cells(1, a).Value) = "funded" Then
        fund = a
        Exit For 
    End If
  Next a

'Populate Funded column with "1" to the last row
  Dim aa As Long
  For aa = 2 To lastRow
    shtA.Cells(a, Funded + 1).Select
  ActiveCell.FormulaR1C1 = "1"
    Next aa

标签： vbaloops

Answer 1

这将添加一个新列、标题和内容：

With ActiveWorkbook.Sheets("Financial").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
    .Value = "Funded"
    .Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, 1).Value = 1
End With