worksheetfunction Lookup

[christian2016]

Hi Guys,

Im trying to use the WorksheetFunction Lookup to store the calculation within the VBA code rather than in a cell within the worksheet.
For some reason I cant get the below code working in VBA although in my sheet its working correctly.

In my worksheet the formula is =IFERROR(LOOKUP(2,1/($B$3:$B$21<=E3)/($C$3:$C$21>=E3),$A$3:$A$21),"Invalid Postcode") and this is working well.

Any help is greatly appreciated.

Thanks

Dim Postcode As String

Postcode = 3000
  x = WorksheetFunction.Lookup(2, 1 / (Range("B3:B21") <= Postcode) / (Range("C3:C21") >= Postcode), Range("A3:A21"), "Invalid Postcode")

 

[JGordon11]

Your worksheet function is wrapped by the IFERROR function. Your vba code shouldn't have the ", "Invalid Postcode")" part because that is a parameter of the IFERROR function which is not being used in the vba statement.

Try it like this with x as variant data type

x = WorksheetFunction.Lookup(2, 1 / (Range("B3:B21") <= Postcode) / (Range("C3:C21") >= Postcode), Range("A3:A21")
if IsError(x) then x = "Invalid PostCode"

 

[christian2016]

Your worksheet function is wrapped by the IFERROR function. Your vba code shouldn't have the ", "Invalid Postcode")" part because that is a parameter of the IFERROR function which is not being used in the vba statement.

Try it like this with x as variant data type

x = WorksheetFunction.Lookup(2, 1 / (Range("B3:B21") <= Postcode) / (Range("C3:C21") >= Postcode), Range("A3:A21")
if IsError(x) then x = "Invalid PostCode"

Thank you. I get type mismatch error on the worksheetfunction.lookup

 

[Fluff]

Change WorksheetFunction to Application