Run excel formula through VBA

I am trying to VLOOKUP some value in 2 sheets and if the value is found then i am putting a hyperlink on the Sheet 1 cell to point it to the Sheet 2 cell.
I have written an Excel formula for the same and it is working fine. But i am unable to convert it into a VBA formula. I tried many combinations, but i am unable to understand what i am doing wrong.
Excel formula:

Please Login or Register  to view this content.

VBA formula which i have tried:

Please Login or Register  to view this content.

P.S.: Do not worry about the row and column indexes. I wrote the formula for test file and writing the vba for the master file.

Oops! That was a small one. Somehow, i missed it out. Thanks Noorie.

Now, the code is working fine. But I got a new problem now.

Is there anything wrong with the ""address"" which i have mentioned in the formula?

Because whenever i am trying to run the macro, a popup is being opened and asking for File names.

I am pasting my entire code here:

Please Login or Register  to view this content.