VLOOKUP with REPLACE Function

Hi everyone,

I am trying to modify the following code:

Please Login or Register  to view this content.

to include the following:

If MID(Target.Row,3,2)=”12”,VLOOKUP(Replace(Target.Row,3,2,”12”),Pname,2,FALSE), VLOOKUP(Target.Row,,Pname,2,FALSE))

That is, if the user enters 3636512690 - for example, I want to replace the 3rd and 4th characters with 12 and look up 3612512690 instead; but if the user enters 3612512690, then look up 3612512690.

I tried:

.Formula = "=VLOOKUP($F" & _
Target.Row & ",REPLACE($F"&Target.Row&",3,2,"12"),Pname,2,FALSE)"

but it is now working - I'm getting, Compile error: Expected: end of statement."

I have attached a sample file - VlookupReplace.

Can someone help me, please?

Thank you,
Gos-C

P.S. I have modified the code (see VlookupReplace_2) and now I am getting "Unable to get the VLookup property of the WorksheetFunction class.

Hi Gos-C

I didn't try to decipher all your code to see what it's doing. You can try changing this line of code from this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

and this line of code from this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Hi jaslake, it works! Thank you very much.

Cheers,
Gos-C