PDA

View Full Version : VBA VLOOKUP always returning zero.



papermonkey
06-27-2018, 02:13 AM
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
06-27-2018, 05:57 AM
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
07-26-2018, 04:24 AM
Thanks everyone for the quick help. its fixed.

p45cal
07-26-2018, 06:19 AM
Thanks everyone for the quick help. its fixed.
That was a month ago.
Oh, it's because more help is needed.