Excel VBA 如何基于两个列范围的搜索结果共同填充用户窗体ListView

这个需求完全可以实现，仅需扩展原有判断逻辑，新增B、C列的匹配规则即可，无需改动核心结构。

具体修改步骤 #

1. 调整判断条件 #

原有代码仅校验了AB、AC列的双向匹配，你只需要在判断条件中新增B、C列的相同匹配规则，用OR连接两个分组的判断逻辑即可，修改后的完整代码如下：

Dim wksSource As Worksheet
Dim rngData As Range
Dim rngCell As Range
Dim LstItem As ListItem
Dim RowCount As Long
Dim league As String
    
'Set the source
league = Worksheets("Stats").Range("BB1").Value
Set wksSource = Worksheets(league)
Set rngData = wksSource.Range("AA2").CurrentRegion

'Add the column headers
homelistView.ColumnHeaders.Clear
With Me.homelistView.ColumnHeaders
    .Add Width:=60
    .Add Width:=163, Alignment:=2
    .Add Width:=163, Alignment:=2
    .Add Width:=20, Alignment:=2
    .Add Width:=20, Alignment:=2
    .Add Width:=2, Alignment:=2
    .Add Width:=20, Alignment:=2
    .Add Width:=20, Alignment:=2
End With

'Count the number of rows in the source range
' 若存在B/C列行数多于AA列的情况，可替换为 RowCount = wksSource.UsedRange.Rows.Count
RowCount = rngData.Rows.Count

'Fill the ListView
Dim x As String
Dim p As String

x = Worksheets("Stats").Range("BD1").Value
p = Worksheets("Stats").Range("BD2").Value

For i = 2 To RowCount
    ' 新增B/C列匹配规则，用括号分组避免逻辑优先级错误
    If ((wksSource.Cells(i, "AB") = x And wksSource.Cells(i, "AC") = p) Or (wksSource.Cells(i, "AC") = x And wksSource.Cells(i, "AB") = p)) _
    Or ((wksSource.Cells(i, "B") = x And wksSource.Cells(i, "C") = p) Or (wksSource.Cells(i, "C") = x And wksSource.Cells(i, "B") = p)) Then
        Set item = homelistView.ListItems.Add(Text:=Format(wksSource.Cells(i, 1), "mm/dd/yyyy"))
        item.SubItems(1) = wksSource.Cells(i, 2)
        item.SubItems(2) = wksSource.Cells(i, 3)
        item.SubItems(3) = wksSource.Cells(i, 4)
        item.SubItems(4) = wksSource.Cells(i, 5)
        item.SubItems(5) = wksSource.Cells(i, 6)
        item.SubItems(6) = wksSource.Cells(i, 7)
        item.SubItems(7) = wksSource.Cells(i, 8)
    End If
Next i

2. 可选优化项 #

• 若你的表格B、C列的有效行数多于AA列，建议将获取RowCount的代码替换为RowCount = wksSource.UsedRange.Rows.Count，避免漏扫行
• 若需要忽略大小写匹配，可将判断语句中的=替换为StrComp(单元格值, x/p, vbTextCompare) = 0
• 若需要区分匹配来源，可新增一列ListView表头，在匹配成功时写入对应来源标识

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