VBA VLOOKUP returning zero value due to data type mismatch.

[papermonkey]

Hi,


am using below function and always getting value zero for .
i even tried changing cell formats for datarange, when i do that i get 13 type mismatch error. please help.


If Trim(CStr(TextBox_itemcode.Text)) <> "" Then
myitval = mylookup(Trim(CStr(TextBox_itemcode.Text)))
Worksheets("SALES").Activate
Range("amt_tot").Rows(ZeileA).Value = myitval
Else
Range("amt_tot").Rows(ZeileA).Value = "9999.99"
End If








Function mylookup(itemcode As String) As Double
Dim myrange As Range
Dim myvalue As Double
Set myrange = Worksheets("Items sales").Range("G24:F1000")
Worksheets("Items sales").Activate
myitval = [Application.WorksheetFunction.SVERWEIS(itemcode, myrange, 5, False)]
If IsError(myvalue) = True Then
myvalue = 9999.99
End If
End Function


please help.
if possible also please suggest me how to convert this vlookup into index match.

 

[MUKESHY12390]

can you try changing to ByVal by defult it's ByRef.

Function mylookup(ByVal itemcode As String) As Double
Dim myrange As Range
Dim myvalue As Double
Set myrange = Worksheets("Items sales").Range("G24:F1000")
Worksheets("Items sales").Activate
myitval = [Application.WorksheetFunction.SVERWEIS(itemcode, myrange, 5, False)]
If IsError(myvalue) = True Then
myvalue = 9999.99
End If
End Function

 

[MUKESHY12390]

and try this piece of code and let me know if it works

Sub Vlookup()
Dim itemCode
Dim myRange As Range
Dim oResult


Set myRange = Worksheets("Sheet1").Range("A:B")  ' '''''Change Sheet name and range
itemCode = "00A1B"  '''' change this lookup value
On Error GoTo ErrFound
oResult = [ WorksheetFunction.SVERWEIS(itemCode, myRange, 1, 0)]


MsgBox oResult
Exit Sub


ErrFound:
MsgBox Err.Number
If Err.Number = 1004 Then
    MsgBox " Value not found"
End If
End Sub



You should have mentioned that you are using German version Excel , took me while to figured out what is "[COLOR=#333333]SVERWEIS"[/COLOR]:)

 

[papermonkey]

Thanks everyone for the quick help. its fixed.