Excel VBA按L列代理名将数据拆分至对应工作表问题排查

1. 最初「无效限定符」错误的原因 #

你之前写的If row_Data.Value = "Mike" Then会报错，是因为row_Data被定义成了Long类型的循环计数器，不是Range对象，根本没有.Value属性，直接用数组里的对应值判断就行。

2. 数据覆盖问题的核心原因 #

代码在计算目标工作表的追加行号时逻辑错误，且赋值用Offset的方式不对，导致每次都写到同一个位置，覆盖之前的数据：

• 错误的行号计算：FinalDest.Cells(FinalDest.Rows.Count, "A")是基于FinalDest（A3单元格）的范围取行，不是取整个工作表的最后一行
• Offset参数错误：row_Wb已经是目标行号，用Offset会从A3开始偏移对应行数，而非直接定位到目标行

修正后的完整代码 #

Option Explicit
Sub Main()
    Dim wb As Workbook
    Dim Data As Variant, Agent_Name As String
    Dim sht As Worksheet
    Dim row_Data As Long, col_Data As Long, row_Wb As Long
    Dim FinalDestWs As Worksheet ' 改用工作表对象，逻辑更清晰
    
    Set wb = ThisWorkbook
    Application.ScreenUpdating = False
    
    ' 清空所有代理工作表的3行及以下内容
    For Each sht In wb.Sheets
        If sht.Name <> "Master Line List - All Data" Then ' 用名称判断更可靠，避免索引变化出错
            sht.Rows("3:" & sht.Rows.Count).ClearContents
        End If
    Next
    
    ' 读取主表数据到数组（A3到Q列最后一行）
    With wb.Sheets("Master Line List - All Data")
        Data = .Range("A3:Q" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    End With
    
    ' 遍历数组中的每一行数据
    For row_Data = 1 To UBound(Data)
        ' 跳过A列为空的行
        If Data(row_Data, 1) <> "" Then
            Agent_Name = Data(row_Data, 12) ' L列是第12列
            
            ' 根据代理姓名获取目标工作表
            Select Case Agent_Name
                Case "Mike"
                    Set FinalDestWs = wb.Sheets("Mike")
                Case "William"
                    Set FinalDestWs = wb.Sheets("William")
                Case "Dan"
                    Set FinalDestWs = wb.Sheets("Dan")
                Case "Kevin"
                    Set FinalDestWs = wb.Sheets("Kevin")
                Case Else
                    ' 遇到未知代理时跳过，可按需修改
                    GoTo NextRow
            End Select
            
            ' 计算目标工作表A列最后非空行的下一行
            row_Wb = FinalDestWs.Cells(FinalDestWs.Rows.Count, "A").End(xlUp).Row + 1
            
            ' 将当前行数据写入目标工作表
            For col_Data = 1 To UBound(Data, 2)
                FinalDestWs.Cells(row_Wb, col_Data).Value = Data(row_Data, col_Data)
            Next col_Data
        End If
NextRow:
    Next row_Data
    
    Application.ScreenUpdating = True
    MsgBox "数据拆分完成！"
End Sub

关键修改点说明 #

• 改用FinalDestWs工作表对象代替原Range对象，逻辑更直观
• 用工作表名称判断清空范围，避免因工作表顺序变化导致错误
• 正确计算追加行号：直接取目标工作表A列的最后非空行+1
• 用Cells(row_Wb, col_Data)直接定位写入位置，规避Offset的偏移错误
• 增加未知代理的跳过逻辑，防止代码报错
• 关闭/开启屏幕刷新，提升运行效率

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