如何用VBA基于Excel另一工作表为行分配测试阶段值

需求说明 #

Sheet1包含列：Item、Total Qty、Test Phase 1 Qty、Test Phase 2 Qty、Test Phase 3 Qty；Sheet2包含列：Item、Test Phase。需要实现：根据Sheet1中对应Item各测试阶段的数量，为Sheet2中每行Item分配对应的测试阶段，剩余行数留空。

VBA宏代码 #

Sub AssignTestPhases()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, j As Long
    Dim itemName As String
    Dim phase1Qty As Integer, phase2Qty As Integer, phase3Qty As Integer
    Dim assignedCount As Integer
    
    ' 关联目标工作表
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' 获取数据区域最后一行
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历Sheet2的Item行（跳过表头，从第2行开始）
    For i = 2 To lastRow2
        itemName = ws2.Cells(i, "A").Value
        assignedCount = 0
        phase1Qty = 0
        phase2Qty = 0
        phase3Qty = 0
        
        ' 在Sheet1中匹配对应Item的各阶段数量
        For j = 2 To lastRow1
            If ws1.Cells(j, "A").Value = itemName Then
                phase1Qty = ws1.Cells(j, "C").Value
                phase2Qty = ws1.Cells(j, "D").Value
                phase3Qty = ws1.Cells(j, "E").Value
                Exit For
            End If
        Next j
        
        ' 按顺序分配测试阶段
        If assignedCount < phase1Qty Then
            ws2.Cells(i, "B").Value = "Test Phase 1"
            assignedCount = assignedCount + 1
        ElseIf assignedCount < phase1Qty + phase2Qty Then
            ws2.Cells(i, "B").Value = "Test Phase 2"
            assignedCount = assignedCount + 1
        ElseIf assignedCount < phase1Qty + phase2Qty + phase3Qty Then
            ws2.Cells(i, "B").Value = "Test Phase 3"
            assignedCount = assignedCount + 1
        Else
            ws2.Cells(i, "B").Value = ""
        End If
    Next i
    
    MsgBox "测试阶段分配完成！", vbInformation
End Sub

代码说明 #

• 工作表关联：通过Set语句绑定Sheet1和Sheet2，简化后续操作。
• 边界处理：用End(xlUp)获取数据最后一行，避免遍历无效空行。
• 匹配逻辑：遍历Sheet1找到对应Item的各测试阶段数量，确保数据对应准确。
• 阶段分配：按Phase1→Phase2→Phase3的顺序分配，用完对应数量后剩余行留空。
• 完成提示：执行完毕后弹出提示框，告知用户操作结果。

使用步骤 #

1. 打开目标Excel工作簿。
2. 按下Alt + F11打开VBA编辑器。
3. 右键点击工作簿名称 → 插入 → 模块，新建代码模块。
4. 将上述代码粘贴到模块中。
5. 按下F5运行宏，或回到Excel界面通过「开发工具」→「宏」选择AssignTestPhases执行。

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