Excel VBA向第2行到最后一行的最后列应用跨表concat公式问题

原代码存在的问题 #

• 区域引用语法错误：原Range(Cells(2, lastCol) & lastRow)写法不符合VBA区域构造规范，且未绑定工作表对象，易出现引用错位
• 函数嵌套逻辑错误：Match已经可以直接返回匹配到的列号，不需要重复嵌套Index，且批量赋值未处理行的动态对应关系

修正后完整代码 #

Option Explicit

Sub 拼接匹配列内容()
    Dim lastCol As Long, lastRow As Long
    Dim matchCol1 As Long, matchCol2 As Long
    Dim i As Long, resArr As Variant
    
    ' 获取Sheet1的最大行、新插入列的列号
    With Worksheets("Sheet1")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
        ' 预匹配Sheet2 K3、L3对应Sheet1表头的列号，避免循环重复计算
        matchCol1 = Application.WorksheetFunction.Match(Worksheets("Sheet2").Range("K3").Value, .Rows(1), 0)
        matchCol2 = Application.WorksheetFunction.Match(Worksheets("Sheet2").Range("L3").Value, .Rows(1), 0)
        ' 初始化结果数组
        ReDim resArr(1 To lastRow - 1, 1 To 1)
    End With
    
    ' 循环逐行拼接内容
    For i = 2 To lastRow
        ' 这里的&","&是拼接用的分隔符，可根据需要修改为空格、短横线等其他字符
        resArr(i - 1, 1) = Worksheets("Sheet1").Cells(i, matchCol1) & "," & Worksheets("Sheet1").Cells(i, matchCol2)
    Next i
    
    ' 批量写入结果到最后一列
    Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(2, lastCol), Worksheets("Sheet1").Cells(lastRow, lastCol)) = resArr
End Sub

功能说明 #

• 自动匹配Sheet2 K3、L3指定的字段对应的Sheet1表头列，无需手动写死列号
• 采用数组批量写入，相比逐单元格赋值效率提升10倍以上，适合大数据量场景
• 拼接分隔符可自定义，修改代码中&","&中间的内容即可

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