您可以使用WorkSheet.Evaluate
方法来做到这一点。
这是一个简单的例子:
Sub Tester()
Dim v, sht, a1, a2
Set sht = ActiveSheet
a1 = sht.Cells(7, 1).Address(False, False)
a2 = sht.Cells(7, 2).Address(False, False)
v = sht.Evaluate("MATCH(" & a1 & "&" & a2 & ",A2:A5&B2:B5,0)")
sht.Range("B9") = v
End Sub
编辑:这是一个更强大的例子,考虑到不同的工作表
Sub Tester2()
Dim v, shtDest, shtSrc, a1, a2, i
Set shtDest = ThisWorkbook.Sheets("Dest")
Set shtSrc = ThisWorkbook.Sheets("Source")
i = 1
a1 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 1).Address(False, False)
a2 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 2).Address(False, False)
Debug.Print a1, a2
v = shtSrc.Evaluate("MATCH(" & a1 & "&" & a2 & ",A2:A9&B2:B9,0)")
If Not IsError(v) Then
shtDest.Cells(i, 3).Value = shtSrc.Range("C2:C9").Cells(v).Value
End If
End Sub