如何让Excel VBA宏自动识别C列最后单元格批量应用公式

问题说明 #

现有3个可正常运行的VBA宏，分别用于从A列提取日期、文件名和文件状态，但当前宏使用固定单元格范围（如F3:F313），无法自动识别C列最后一行有数据的位置并匹配对应范围。每次C列数据更新到新行（如C500），都需手动调整公式范围，需优化宏实现自动适配。

优化思路 #

通过Cells(Rows.Count, "C").End(xlUp).Row获取C列最后一行非空单元格的行号，动态生成目标单元格范围；同时移除冗余的Select/Copy/Paste操作，直接批量赋值公式提升运行效率。

1. 提取日期（优化版） #

Sub Macro13() '提取日期
    Dim lastRow As Long
    '获取C列最后一行数据的行号
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    '给D2到D列对应最后一行批量赋值公式
    Range("D2:D" & lastRow).FormulaR1C1 = "=extractDate(RC[-1])"
End Sub

2. 查找文件状态（优化版） #

Sub Macro15() '查找文件状态
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Range("F2:F" & lastRow).FormulaR1C1 = _
        "=IFERROR(LOOKUP(2^15,SEARCH({""Feed"",""Feed 1"",""Feed 2""},RC[-3]),{""Feed"",""Feed 1"",""Feed 2""}),""Combine"")"
End Sub

3. 提取文件名（优化版） #

Sub Macro17() '提取文件名
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Range("E2:E" & lastRow).FormulaR1C1 = _
        "=IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ABCD - GAMA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+2),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ALPHA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+2),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9," & _
        "0},RC[-2]&""1234567890""))-1))=""ABCD - BETA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""DBETA "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""A"",LEFT(RC[-2]," & _
        "MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+6),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))="""",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""ABETA"",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+6),LEF" & _
        "T(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2] & ""1234567890""))-1))))))))"
End Sub

关键修改说明 #

1. 动态范围适配：通过Cells(Rows.Count, "C").End(xlUp).Row精准定位C列最后一行数据，确保目标范围始终与C列数据长度匹配。
2. 简化操作流程：直接给整个目标范围批量赋值公式，替代原宏中Select→Copy→Paste的冗余步骤，提升宏运行速度。
3. 保留原有逻辑：所有业务相关的公式完全保留，仅修改范围生成方式，不影响原有功能输出。

内容的提问来源于stack exchange，提问作者Salman Shafi