Excel VBA 双条件匹配查单价 Evaluate返回#VALUE!错误求解

错误原因 #

• 公式字符串拼接错误：VBA变量sh5无法直接在Excel公式中识别，公式内引用工作表需要写实际表名'Unit Price'，带空格的表名必须用单引号包裹
• 双条件连接的语法错误：两个匹配区域的连接运算需要放在公式内部正确解析，原写法把VBA变量和公式内容混写导致解析失败
• MATCH函数仅返回匹配行的相对位置，无法直接作为单价输出，需要搭配INDEX函数提取对应单价列的数值

修正后代码（Evaluate实现） #

Sub unitPrice()
    Dim sh4 As Worksheet, sh5 As Worksheet
    Dim matchFormula As String
    
    Set sh4 = ThisWorkbook.Sheets("Invoice")
    Set sh5 = ThisWorkbook.Sheets("Unit Price")
    
    ' 构造双条件匹配公式，匹配成功返回对应单价
    matchFormula = "INDEX('Unit Price'!C2:C5,MATCH(" & _
                    sh4.Cells(11, 1).Address(False, False) & "&" & _
                    sh4.Cells(18, 1).Address(False, False) & _
                    ",'Unit Price'!B2:B5&'Unit Price'!A2:A5,0))"
                    
    ' 屏蔽匹配错误，匹配不到时返回空值
    On Error Resume Next
    sh4.Range("H18").Value = sh4.Evaluate(matchFormula)
    If Err.Number <> 0 Then sh4.Range("H18").Value = ""
    On Error GoTo 0
End Sub

更稳定的优化方案（字典预加载数据） #

如果数据量不大，用字典预存名称+产品作为键、单价作为值的映射关系，调用时直接读取，性能和稳定性都优于公式解析方案：

Sub unitPriceByDict()
    Dim sh4 As Worksheet, sh5 As Worksheet
    Dim priceDict As Object
    Dim i As Long, key As String
    
    Set sh4 = ThisWorkbook.Sheets("Invoice")
    Set sh5 = ThisWorkbook.Sheets("Unit Price")
    Set priceDict = CreateObject("Scripting.Dictionary")
    
    ' 预加载单价表数据到字典，行范围可按需调整
    For i = 2 To 5
        key = sh5.Range("B" & i).Value & sh5.Range("A" & i).Value
        If Not priceDict.exists(key) Then
            priceDict(key) = sh5.Range("C" & i).Value ' 假设单价存放在C列，可调整
        End If
    Next i
    
    ' 匹配取值
    key = sh4.Cells(11, 1).Value & sh4.Cells(18, 1).Value
    sh4.Range("H18").Value = IIf(priceDict.exists(key), priceDict(key), "")
    
    Set priceDict = Nothing
End Sub

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