CODEWITHSUNDEEP
excelexcel-formulavba
Adnan
Adnan

Reputation: 165

LOOKUP formula to find last value in VBA

I want to convert a lookup formula into Worksheet function but it prompts Type mismatch error.

This formula working fine.

=LOOKUP(2,1/(SalesDB!I6:I3005="FLOUR"),SalesDB!K6:K3005)

This prompts Type mismatch error

ActiveSheet.Range("Q9").Value = Application.WorksheetFunction.Lookup(2, 1 / (Sheets("SalesDB").Range("$I$6:$I$3005") = "FLOUR"), Sheets("SalesDB").Range("$K$6:$K$3005"))

I want to lookup last value from a column so Vlookup formula does not work on it. How can I solve the error?

Upvotes: 1

Views: 1785

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19299

The 'trick' you are using with the LOOKUP function won't easily translate to VBA. You can use a simple Find method with the search order reversed (parameter SearchDirection:=xlPrevious) which will find the last match and then use the Offset function to go across the columns to retrieve the value you want.

Option Explicit

Sub Test()

    ActiveSheet.Range("Q9").Value = LastVlookup("FLOUR", Sheets("SalesDB").Range("$I$6:$K$3005"), 3)

End Sub

Function LastVlookup(varLookup As Variant, rngData As Range, lngOffset As Long) As Variant

    LastVlookup = rngData.Find( _
        What:=varLookup, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchDirection:=xlPrevious).Offset(0, lngOffset - 1).Value

End Function

Upvotes: 1

Related Questions