如何修改VBA代码使输出结果仅限定在M2:V2单元格范围

当前VBA代码执行后，输出结果的范围不符合预期，需要修改代码使目标结果仅输出至M2:V2单元格区域。

原代码 #

Private Sub CommandButton2_Click()
    Dim rng As Range, j As Variant
    Dim StartV As Long, EndV As Long, i As Long
    Dim k() As Long, Last_Row As Long

    ' Clear contents of the target range
    ActiveSheet.Range("M2:M" & Range("M3").End(xlDown).Row).ClearContents
    Last_Row = Range("A2").End(xlUp).Row
    'Last_Row = Range("A2").End(xlRight).Row
    ReDim k(0)

    ' Set range to B2:K2
    Set rng = Range("B2:K2")
    StartV = Range("B2")
    EndV = Range("K2")
    StartV = 0
    EndV = 9
    For i = StartV To EndV
        On Error Resume Next
        j = Application.Match(i, rng, 0)
        If IsError(j) Then
            k(UBound(k)) = i
            ReDim Preserve k(UBound(k) + 1)
        End If
    Next i

    ' Output the missing values to M2:V2
    Range("M2") = "Resul Missing values"
    
Range("M2:V" & UBound(k) + 1) = Application.Transpose(k)

   
End Sub

问题分析 #

1. 清除目标区域的代码逻辑错误，误操作M列多行内容
2. 数组k最后会生成一个空元素，导致输出时多出无效行
3. 输出时使用Transpose将数组转置为纵向，且范围计算错误，导致结果分散到多行
4. 原代码给M2赋值标题，会占用目标输出区域的第一个单元格，导致缺失值无法从M2开始填充

修改后的代码 #

Private Sub CommandButton2_Click()
    Dim rng As Range, j As Variant
    Dim StartV As Long, EndV As Long, i As Long
    Dim k() As Long, arrIndex As Integer

    ' 清除目标区域M2:V2的内容
    ActiveSheet.Range("M2:V2").ClearContents

    ' 设置待检查的范围为B2:K2
    Set rng = Range("B2:K2")
    StartV = 0
    EndV = 9
    arrIndex = 0

    ' 遍历0到9，找出不在B2:K2中的值
    For i = StartV To EndV
        j = Application.Match(i, rng, 0)
        If IsError(j) Then
            ReDim Preserve k(arrIndex)
            k(arrIndex) = i
            arrIndex = arrIndex + 1
        End If
    Next i

    ' 将缺失值横向填充到M2:V2区域
    If arrIndex > 0 Then
        ActiveSheet.Range("M2").Resize(1, arrIndex).Value = k
    End If
End Sub

修改说明 #

• 直接指定清除M2:V2区域，精准清理目标范围
• 移除无用的Last_Row变量，简化代码结构
• 调整数组赋值逻辑，用arrIndex跟踪下标，避免生成空元素
• 输出时横向填充数组：通过Resize(1, arrIndex)将缺失值从M2开始横向排列，刚好匹配M2:V2的10个单元格（0-9最多10个缺失值）
• 移除原代码中占用M2的标题赋值，若需要保留标题，可将其移至M1等非目标输出区域

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