excel - VBA查找输出不兼容的类型

我正在尝试使用查找从下到上进行反向vlookup，在excel选项卡中它可以正常工作，但是在将其转换为VBA时遇到一些问题。

Excel公式:=LOOKUP(2;1/(Table1[Code]=L4);Table1[Key])
VB:

Set sht2 = Worksheets("Base")
Set sht3 = Worksheets("Extra")

rng1 = sht2.ListObjects("Table1").ListColumns(1).DataBodyRange.Value
rng2 = sht2.ListObjects("Table1").ListColumns(2).DataBodyRange.Value
cll = sht3.Cells(2, 12)

rw = Application.WorksheetFunction.Lookup(2, 1 / (rng1 = cll), rng2)
sht3.Cells(2,13) = rw

有小费吗？

最佳答案

如果要使用Find而不是查找，可以这样进行:

Sub Find_from_bottom()

Dim Rowaddress As Long
Dim sht2 As Worksheet
Dim sht3 As Worksheet

Set sht2 = Worksheets("Base")
Set sht3 = Worksheets("Extra")

Rowaddress = Range("A:A").Find(sht3.Cells(2, 12).Value, Range("A1"), SearchDirection:=xlPrevious).Row
sht3.Cells(2, 13) = sht2.Cells(Rowaddress, 2)

End Sub

关于excel - VBA查找输出不兼容的类型，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53283049/