VBA worksheetfunction.lookup issues

[quarna]

Hi.

My code returns the wrong cell values, but only sometimes, depending on the value it is looking for and i cant seem to find out why.

The Code looks in sheet("varenumre") for a number like you see below and returns a text in one cell and a number in the other:

81201.04	AB 6t granit 160/220 RA15	1007409
81201.44	AB 6t granit 160/220 P-flex RA15	1007411
81402.02	AB 11t Granit 70/100 RA30	1007530
81402.42	AB 11t granit 70/100 P-flex RA30	1007533
81401.42	AB 11t granit 70/100 P-flex RA15	1007526
51400.02	AB 11t granit 70/100	1001013

For example if it lookup 81402.02 it does not return, AB 11t Granit 70/100 RA30, 1007530
But instead it returns, AB 11t granit 70/100, 1001013
However if it looks for 81201.44 it returns the correct values.


I am new to VBA and the code is a mix from different online searches.


The entire code:

Sub xopslag()

Dim markedcell As Range
Dim materiale As Range
Dim varenummer As Range

Set markedcell = ActiveCell
Set materiale = markedcell.Offset(, -1)
Set varenummer = markedcell.Offset(, 1)


If Not Application.Intersect(ActiveCell, Range("H2:H1000")) Is Nothing Then
On Error Resume Next
materiale.Value = WorksheetFunction.Lookup(markedcell.Value, Sheets("varenumre").Range("A2:A500"), Sheets("varenumre").Range("B2:B500"))
varenummer.Value = WorksheetFunction.Lookup(markedcell.Value, Sheets("varenumre").Range("A2:A500"), Sheets("varenumre").Range("C2:C500"))
If Err.Number <> 0 Then
MsgBox ("Wong number !")
End If

On Error GoTo 0

Else
MsgBox ("Stay on cell" & vbCrLf & " " & vbCrLf & "Remember to type number!")
End If

End Sub


Thanks a bunch

 

[jasonb75]

Lookup uses approximate matching, the source table needs to be sorted with column A in ascending order for it to work correctly.

With the example provided, I would expect some errors but not the one that you have told us about. From the information provided it would appear that some of your codes are numbers formatted as text. If this is the case then they would need to be converted to proper numbers otherwise you will encounter more errors.

 

[quarna]

Lookup uses approximate matching, the source table needs to be sorted with column A in ascending order for it to work correctly.

With the example provided, I would expect some errors but not the one that you have told us about. From the information provided it would appear that some of your codes are numbers formatted as text. If this is the case then they would need to be converted to proper numbers otherwise you will encounter more errors.

Oh my god.

Sorry about that, what a simple cause of the issue, that i should and do know.
Guess i was starring blind from looking at this too long.

Thank you, have a nice day

 

[jasonb75]

I notice that you have office 365 as your excel version so you could use XLOOKUP instead of LOOKUP which would retrieve an exact match instead of approximate. It will likely be slower to process, but with a 500 row range hopefully it will not be noticeable.

 

[quarna]

I notice that you have office 365 as your excel version so you could use XLOOKUP instead of LOOKUP which would retrieve an exact match instead of approximate. It will likely be slower to process, but with a 500 row range hopefully it will not be noticeable.

To begin with it was an xlookup function, but because of the formula multiple times got deleted and resulting in some frustration. I search for an vba solution.
And it works so far, but as you said, Col. A must be sorted A-Z for it to work correctly.

 

[jasonb75]

You can use xlookup in vba the same as lookup, the syntax is exactly the same.