跨Excel文档获取特定行最后填充单元格的VBA实现问题

需求与问题 #

• 要写VBA宏核对两个Excel文档的产品数值：季度文档取数逻辑已搞定，月度文档每月需引用的单元格会向右偏移一列，打算通过获取该行最后填充单元格来实现，但搞不定正确语法
• 指定具体单元格时代码能运行，但用变量引用最后填充单元格就失败

当前代码 #

Sub LRValuesRefresh()

    Dim Jumpoff_YrMonth As String

    Jumpoff_Month = Range("B2").Value

    With Range("D9")
        .Formula = "='\\drive\\drivename\\month\\datatype\\specific_report\\[Report_Name " & _
           Jumpoff_Month & " Acct_Standard.xlsb]Product Name'!$" & _
           Range("DI27").End(xlToRight).Column
    End With

End Sub

问题分析与修正 #

原代码的核心问题：

1. Range("DI27").End(xlToRight).Column 只返回列的数字编号（比如120），但Excel公式里需要列字母（比如DJ），直接拼接会导致公式无效
2. 未明确指定工作表，容易因当前激活表不对而出错

修正后的代码 #

Sub LRValuesRefresh()
    Dim Jumpoff_Month As String
    Dim lastCol As Long
    Dim colLetter As String
    Dim targetWbPath As String
    Dim targetWsName As String
    
    ' 定义路径和工作表名（方便后续修改）
    targetWbPath = "\\drive\drivename\month\datatype\specific_report\[Report_Name "
    targetWsName = "Product Name"
    
    ' 获取当前工作簿指定工作表B2的年月值（替换成你的实际工作表名）
    Jumpoff_Month = ThisWorkbook.Sheets("你的工作表名").Range("B2").Value
    
    ' 打开目标月度工作簿，避免跨文档引用出错
    Dim targetWb As Workbook
    Set targetWb = Workbooks.Open(targetWbPath & Jumpoff_Month & " Acct_Standard.xlsb")
    
    ' 获取目标工作表第27行最后有数据的列号（从最右侧往左找，比从DI开始更可靠）
    lastCol = targetWb.Sheets(targetWsName).Cells(27, targetWb.Sheets(targetWsName).Columns.Count).End(xlToLeft).Column
    
    ' 将列号转换成Excel公式需要的列字母
    colLetter = Split(targetWb.Sheets(targetWsName).Cells(1, lastCol).Address, "$")(1)
    
    ' 关闭目标工作簿（不需要保留打开状态）
    targetWb.Close SaveChanges:=False
    
    ' 给当前工作簿指定工作表的D9设置公式（替换成你的实际工作表名）
    With ThisWorkbook.Sheets("你的工作表名").Range("D9")
        .Formula = "='" & targetWbPath & Jumpoff_Month & " Acct_Standard.xlsb]" & targetWsName & "'!$" & colLetter & "$27"
    End With
End Sub

关键说明 #

• 明确指定ThisWorkbook（当前运行宏的工作簿）和目标工作簿的工作表，避免引用混乱
• 用Cells(27, Columns.Count).End(xlToLeft)获取第27行最后有数据的列，比从DI开始更通用（如果某月度DI列之后有空白，从最右侧找更准确）
• 通过Split(Cells(1, lastCol).Address, "$")(1)把列号转成字母，这是VBA里常用的转换方法
• 如果不想打开目标工作簿，可以用GetObject方法，但Open方式对新手更易理解和调试

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