Use Native Excel Function Lookup Formula In VBA

[ramananhrm]

Hi,

I have a query on VBA.

There is a range of cells contains the following words (F4 to F19)

Bridal
Bars
Body Polishing
Clinics
Android
Comics
Lighting
Android
Crystals
Grocery
Waxing
Labs
Flip Phones
Drama
Timer
Flip Phones

There is a sentance 'Aluminium Yoga Bridal Dealers Waxing' in A2


If i try it out in Excel function, i can get the data through the following Excel function
=LOOKUP(2^15,SEARCH(F4:F19,A2),F4:F19)

But when i tried to convert it to the VBA, i receive 'Type mismatch error'

Im not sure where im going wrong.

I have mentioned the code.

Sub fndvalinrng()
Dim rsltrng, srchrng As Range
Dim srch As Integer
srch = ThisWorkbook.Worksheets("Sheet1").Cells(2, 1).Value
Set rsltrng = ThisWorkbook.Worksheets("Sheet1").Range("F4:F19")
Set srchrng = Application.WorksheetFunction.Search(srchrng, srch)

ThisWorkbook.Worksheets("Sheet1").Cells(2, 2).Value = _
Application.WorksheetFunction.Lookup(2 ^ 15, srchrng, rsltrng)

End Sub

Im converting to VBA code because i have a huge dataset.

Please help me out


Regards,
Ramanan