VBA VLOOKUP always returning zero.

[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.

[p45cal]

Note regarding these two lines:

Set myrange = Worksheets("Items sales").Range("G24:F1000")
myitval = [Application.WorksheetFunction.SVERWEIS(itemcode, myrange, 5, False)]

that SWEREIS (aka VLOOKUP) is looking in the first column (column F) of a 2 column range (F24:G1000) and trying to return something from the 5th column of that 2 column range.
I note also that you've used TRIM and you're looking for an Exact match; are you sure column F also contains no extra spaces?

[papermonkey]

Thanks everyone for the quick help. its fixed.

[p45cal]

Thanks everyone for the quick help. its fixed.

That was a month ago.
Oh, it's because more help is needed.