如何用VBA将L列IF+VLOOKUP公式填充至K列最后一个有数据单元格

需求说明 #

我是VBA新手，尝试了多种方法都未能实现想要的效果。当前需求为开发一个宏，将L列的IF+VLOOKUP组合公式自动向下填充至上一列（K列）的最后一个有数据的单元格位置，不可使用固定单元格范围，因为每日数据都会更新，会新增更多数据行。

初始问题代码 #

Sub ZBR_Users_Review()
'
' ZBR_Users_Review Macro
' 用于分析确保所有当前ZBR用户都已分配对应的Tagetik角色
'

    Range("L5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-11],'Tagetik CCO role users assigned'!R5C1:R1048576C6,6,FALSE)=0, ""No"", ""Assigned"")"
    Range("L5").Select
    ' 此处为固定范围填充，无法适配动态变化的数据行数
    Selection.AutoFill Destination:=Range("L5:L160")
    Range("L5:L160").Select
    Range("L4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 15
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "Check"
    Rows("4:4").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$4:$L$100000").AutoFilter Field:=12, Criteria1:="#N/A"
    Range("L161").Select
End Sub

最终解决方案 #

在@Toddleson的建议下，找到了该场景的解决方案，代码如下，可供有同类需求的人员参考使用：

Sub ZBR_Users_Review()
'
' ZBR_Users_Review Macro
' 用于分析确保所有当前ZBR用户都已分配对应的Tagetik角色
'
    Dim lRow As Long
    ' 若需严格匹配K列最后一行，可修改为：lRow = Cells(Rows.Count, 11).End(xlUp).Row
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    Range("L5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-11],'Tagetik CCO role users assigned'!R5C1:R1048576C6,6,FALSE)=0, ""No"", ""Assigned"")"
    Range("L5").Select
    ' 动态填充到最后一行，适配数据变化
    Selection.AutoFill Destination:=Range("L5:L" & lRow)
    Range("L5:L" & lRow).Select
    Range("L4").Select

    ' 剩余格式设置、筛选等代码和初始版本保持一致即可
End Sub

内容的提问来源于stack exchange，提问作者Gonçalo Malveiro