'Type mismatch error' while translating the worksheet function to 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, im 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

[nilem]

Hi Ramanan,
maybe something like this

Please Login or Register  to view this content.

and please insert the code tags around your code in the first post

[ramananhrm]

Hi Nilem,

Thank you so much for the reply.

I have one more query.

The sentance 'Aluminium Yoga Bridal Dealers Waxing' has two matching keywords.(Highlighted)

The first matching word value will be placed in B2 & the next should go with C2, D2 etc.

Is there any possibility?

Please help me out.

[nilem]

Please Login or Register  to view this content.

What about code tags in the first post?