Excel VBA多条件Select Case语句编写求助（多列匹配）

一、Select Case实现方案 #

针对你遇到的变量赋值和范围定义问题，核心思路是逐行遍历单元格，分别获取每行F列（Chg）和I列（Acro）的具体值，再通过Select Case组合双条件匹配对应场景。以下是修正后的完整代码：

Sub VLookupByTwoConditions_SelectCase()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim chgValue As String
    Dim acroValue As String
    Dim lookupResult As Variant
    
    ' 指定目标工作表，避免依赖ActiveSheet引发错误
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' 替换为你的工作表名称
    ' 获取数据最后一行（以F列为准，可按需调整）
    lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
    
    ' 从第2行开始遍历（假设第1行是表头）
    For i = 2 To lastRow
        ' 赋值当前行的Chg和Acro值，Trim去除首尾空格避免匹配误差
        chgValue = Trim(ws.Cells(i, "F").Value)
        acroValue = Trim(ws.Cells(i, "I").Value)
        
        ' 组合双条件进行判断
        Select Case True
            ' 场景1：Chg为Removed，Acro包含RRR
            Case chgValue = "Removed" And InStr(1, acroValue, "RRR", vbTextCompare) > 0
                lookupResult = Application.VLookup(ws.Cells(i, "I").Value, ws.Range("P:Q"), 2, False) ' 替换为你的查找范围和列索引
            ' 场景2：Chg为Added，Acro包含RRR
            Case chgValue = "Added" And InStr(1, acroValue, "RRR", vbTextCompare) > 0
                lookupResult = Application.VLookup(ws.Cells(i, "I").Value, ws.Range("R:S"), 2, False)
            ' 场景3：Chg为Modified，Acro包含RRR
            Case chgValue = "Modified" And InStr(1, acroValue, "RRR", vbTextCompare) > 0
                lookupResult = Application.VLookup(ws.Cells(i, "I").Value, ws.Range("T:U"), 2, False)
            ' 继续补充剩余12种场景，格式同上
            ' ...
            
            ' 默认场景：无匹配条件时的处理（可留空或赋值默认文本）
            Case Else
                lookupResult = ""
        End Select
        
        ' 将结果写入J列，处理VLOOKUP找不到值的情况
        If IsError(lookupResult) Then
            ws.Cells(i, "J").Value = "无匹配项"
        Else
            ws.Cells(i, "J").Value = lookupResult
        End If
    Next i
End Sub

核心要点说明： #

• 变量赋值：通过ws.Cells(i, "F").Value精准获取当前行的单元格值，搭配Trim()消除空格干扰。
• 子串包含判断：用InStr(1, 目标字符串, 子串, vbTextCompare)判断是否包含，返回值>0则表示匹配（vbTextCompare忽略大小写，需区分大小写可改为vbBinaryCompare）。
• Select Case用法：通过Select Case True实现多条件组合判断，每个Case对应一种双条件场景。
• 错误处理：用IsError()捕获VLOOKUP的错误返回值，避免单元格显示#N/A。

二、IF语句实现方案 #

如果觉得Select Case不够直观，也可以用ElseIf串联所有场景，逻辑更直白，新手易上手：

Sub VLookupByTwoConditions_IF()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim chgValue As String
    Dim acroValue As String
    Dim lookupResult As Variant
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
    
    For i = 2 To lastRow
        chgValue = Trim(ws.Cells(i, "F").Value)
        acroValue = Trim(ws.Cells(i, "I").Value)
        
        ' 逐个判断15种场景
        If chgValue = "Removed" And InStr(1, acroValue, "RRR", vbTextCompare) > 0 Then
            lookupResult = Application.VLookup(ws.Cells(i, "I").Value, ws.Range("P:Q"), 2, False)
        ElseIf chgValue = "Removed" And InStr(1, acroValue, "AAA", vbTextCompare) > 0 Then
            lookupResult = Application.VLookup(ws.Cells(i, "I").Value, ws.Range("V:W"), 2, False)
        ElseIf chgValue = "Removed" And InStr(1, acroValue, "BBB", vbTextCompare) > 0 Then
            lookupResult = Application.VLookup(ws.Cells(i, "I").Value, ws.Range("X:Y"), 2, False)
        ' 继续补充剩余场景
        ' ...
        Else
            lookupResult = ""
        End If
        
        ' 写入结果并处理错误
        If IsError(lookupResult) Then
            ws.Cells(i, "J").Value = "无匹配项"
        Else
            ws.Cells(i, "J").Value = lookupResult
        End If
    Next i
End Sub

通用优化建议 #

1. 替换代码中的工作表名称和VLOOKUP查找范围为你实际的表格信息。
2. 若数据量较大，可在代码开头添加Application.ScreenUpdating = False，结尾添加Application.ScreenUpdating = True，关闭屏幕更新提升运行速度。

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