如何用Excel VBA提取同一行多列值写入单个单元格且不被覆盖

问题根源 #

• 每次循环直接对E9单元格赋值，新内容会覆盖之前写入的内容，最终仅保留最后一次匹配结果
• 逻辑运算符优先级错误：VBA中And优先级高于Or，原有判断逻辑不符合「年龄为Young/Old同时健康状态匹配对应分类」的需求
• 没有添加换行符，多个结果无法分行展示

修正后代码 #

Sub test()
    Dim Names As Range
    Dim Age As Range
    Dim Health As Range
    Dim LastRow As Long
    Dim resStr As String ' 用于拼接所有结果的字符串变量
    
    LastRow = Worksheets("Sheet1").Cells(65536, "A").End(xlUp).Row
    Set Names = Worksheets("Sheet1").Range("A2:A" & LastRow)
    resStr = "" ' 初始化结果字符串
    
    For Each Name In Names
        Set Age = Worksheets("Sheet1").Range("B" & Name.Row)
        Set Health = Worksheets("Sheet1").Range("D" & Name.Row)
        
        ' 加括号明确逻辑优先级：年龄是Young/Old 且 健康状态为生病
        If (Age.Value = "Young" Or Age.Value = "Old") And Health.Value = "Sick" Then
            resStr = resStr & "Unhealthy: " & Name.Value & Chr(10) ' Chr(10)是换行符
        End If
        
        ' 加括号明确逻辑优先级：年龄是Young/Old 且 健康状态为健康
        If (Age.Value = "Young" Or Age.Value = "Old") And Health.Value = "Not Sick" Then
            resStr = resStr & "Healthy: " & Name.Value & Chr(10)
        End If
    Next
    
    ' 循环结束后一次性写入E9，去掉末尾多余的换行符
    If resStr <> "" Then
        Worksheets("Sheet1").Range("E9") = Left(resStr, Len(resStr) - 1)
    End If
End Sub

补充设置 #

如果要E9单元格自动换行展示所有内容，选中E9后右键选择「设置单元格格式」→「对齐」→勾选「自动换行」即可。

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