VBA lookup outputs incompatible types

user_9262788 0
Viewed 134

I'm trying to do a inversed vlookup, from bottom to top, using lookup, it works fine in the excel tab but I'm having a few issues translating it to VBA.

Excel formula: =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

Any tips?

vba excel
1 Answers

If you want to use Find instead of a lookup you can do it like this: 

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
user_85644010
Related