跨工作表多条件MATCH函数出现类型不匹配错误求助

错误原因 #

原代码中WorksheetFunction.Match无法直接处理跨工作表的数组逻辑运算，(namerange = Range("B" & p)) * (frange <> "F")这种写法在VBA里会生成Variant数组，但WorksheetFunction.Match不支持这种形式的参数；同时未明确指定Range("B" & p)所属工作表，导致引用歧义，触发类型不匹配错误。

解决方案 #

使用WS.Evaluate构建完整的MATCH公式，明确跨工作表引用；同时移除冗余的Activate和Select操作，提升代码效率与稳定性：

Dim WS As Worksheet
Dim WP As Worksheet
Dim namerange As Range
Dim frange As Range
Dim LRP As Long ' 改用Long避免行号超出Integer范围
Dim p As Long
Dim place As Variant ' 用Variant接收Match结果，避免无匹配时报错
Dim pacient As Variant

Set WP = Sheets("Pacients")
Set WS = Sheets("Agenda")
Set namerange = WS.Range("C2:C5000")
Set frange = WS.Range("G2:G5000")
LRP = WP.Cells(Rows.Count, 1).End(xlUp).Row

For p = 1 To LRP
    pacient = WP.Range("B" & p).Value
    If WP.Range("O" & p).Value = True Then
        Do While WP.Range("O" & p).Value = True
            ' 用Evaluate构建跨工作表的MATCH公式
            place = WS.Evaluate("MATCH(1, (C2:C5000=" & WP.Range("B" & p).Address(External:=True) & ")*(G2:G5000<>""F""), 0)")
            
            ' 检查是否找到匹配项
            If Not IsError(place) Then
                place = place + 1 ' 因为C2是第2行，MATCH返回相对位置，转成绝对行号
                ' 直接操作单元格，无需Activate/Select
                WS.Range("J" & place).Value = "1"
                WS.Range("J" & place + 1).FormulaR1C1 = _
                    "=IF(RC[-7]=""" & pacient & """ & MONTH(RC[-8])=MONTH(R" & place & "C2),1,"""")"
                WS.Range("J" & place + 1 & ":J5000").FillDown ' 用FillDown替代复制粘贴
            End If
            
            WP.Range("O" & p).Calculate
        Loop
    End If
Next p

关键修改点说明 #

• 跨工作表引用处理：通过WP.Range("B" & p).Address(External:=True)生成带工作表名的绝对引用，确保Evaluate能正确识别跨工作表的条件值。
• 使用Evaluate执行公式：WS.Evaluate会在指定工作表（Agenda）的上下文里执行MATCH公式，支持数组逻辑运算。
• 错误处理：将place定义为Variant，用IsError检查是否找到匹配，避免无匹配时抛出错误。
• 移除Activate/Select：直接通过工作表对象引用单元格，提升代码运行速度，避免因激活工作表导致的逻辑错误。
• 行号类型优化：将LRP和p改为Long类型，支持超过32767行的大数据量。

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