rong3
09-28-2017, 03:32 AM
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:
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: