excel - Excel VBA 将值添加到与特定标题匹配的整个列

Question

所以我需要将 2021 添加到我的工作表的评估年份列中，但范围不断突破

LastRow 正在返回正确的值，但我无法弄清楚为什么范围不起作用

   Sub AutoFill()

   Dim rFind As Range
   Dim ColumnLetter As Variant
   Dim ColumnNumber As Variant
   Dim LastRow As Variant
   Dim Fill As Range

   With Range("A1:P1")
   Set rFind = .Find(What:="AssessmentYear", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False) 
   If Not rFind Is Nothing Then 
   ColumnNumber = rFind.Column
   ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)
   LastRow = Cells(Rows.Count, 1).End(xlUp).Row
   MsgBox ColumnLetter 
   MsgBox LastRow  
   Cells(LastRow, ColumnNumber).Value = "Here"
   Range("ColumnLetter & 2 : ColumnLetter & LastRow").Value = "2021"

       End If
   End With
   End Sub

我不断收到方法全局失败错误

标签： excelvba

Answer 1

问题是 VBA 将“ColumnLetter & 2 : ColumnLetter & LastRow”视为字符串，实际上并未使用变量 ColumnLetter 或 LastRow。只需将其更改为以下内容，它应该可以正常工作。

Range(ColumnLetter & "2 : " & ColumnLetter & LastRow).Value = "2021"