Need an advice for VLOOKUP function in VBA

[rong3]

With 2 sheets of all, I tried to Vlookup value of Sheet1 in VBA as below



Function return_vlookup(ByVal input_value As String) As Variant
Dim MyStringVar1 As Variant
On Error Resume Next
MyStringVar1 = Application.WorksheetFunction.VLookup(input_value, ThisWorkbook.Sheets("Sheet1").Range("B2:C400"), 2,0)
return_vlookup = MyStringVar1
On Error GoTo 0
If IsEmpty(MyStringVar1) Then
MsgBox "No data(s)"
return_vlookup = ""
End If
End Function


I want to get value as string of function returns, but it says <Unable to get the VLookup property of the WorksheetFunction class>, then i remove the last char "0" in .WorksheetFunction.VLookup(input_value, ThisWorkbook.Sheets("Sheet1").Range("B2:C400"), 2,0) so it's ok, but is not match the value at Vlookup data, seems like the address in range has problem.
Can anyone help me to solve it?:crying:

[mana]

Please try

>input_value As String

input_value As Variant

[rong3]

Thank you for your help, I solved it.