VLOOKUP with REPLACE Function

[Gos-C]

Hi everyone,

I am trying to modify the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 6 Then
If (Range("F" & Target.Row) = "" And Range("G" & Target.Row) <> "") Or Mid(Target.Value, 3, 2) <> "12" Then
Range("G" & Target.Row) = ""
Else
If Mid(Target.Value, 3, 2) = "12" Then
With Range("G" & Target.Row)
.Formula = "=IF($F" & Target.Row & "="""","""",VLOOKUP($F" & _
Target.Row & "," & "Pname,2,FALSE))"
End With
If IsError(Target.Offset(0, 1).Value) Then
Range("G" & Target.Row).Value = ""
MsgBox "The number you entered is not found in the list, please enter the associated name.", _
            vbExclamation, "Number Not Found"
Else
Range("G" & Target.Row).Value = Range("G" & Target.Row).Value
End If
End If
End If
End If
End Sub

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.

[jaslake]

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

x = Replace(Target.Value, 3, 2, "12")

to this

x = WorksheetFunction.Replace(CStr(Target.Value), 3, 2, "12")

and this line of code from this

Range("G" & Target.Row) = Application.WorksheetFunction.VLookup(x, Pname, 2, False)

to this

 Range("G" & Target.Row) = Application.WorksheetFunction.VLookup(Val(x), Pname, 2, False)

[Gos-C]

Hi jaslake, it works! Thank you very much.

Cheers,
Gos-C