CODEWITHSUNDEEP
vbaexcelvlookup
TTT
TTT

Reputation: 4434

Error 2042 ("N/A") in Excel VLookup() Worksheet Function

Need some help to figure out strange VBA VLOOKUP Type Missmatch error. The code is really simple, since sss0 is a random number and all I want is to find closest value in a range (sheet 'BMD_CDF', Range("A2:B999")). In the spreadsheet, I set format for Sheets("BMD_CDF").Range("A2:B999") to scientific already...

Dim LookUp_Range As Range
Dim sss0 As Double
Set LookUp_Range = Sheets("BMD_CDF").Range("A2:B999")

sss0=Application.WorksheetFunction.Max(Rnd(), 0.005)
Debug.Print Application.VLookup(sss0, LookUp_Range, 2, 0)

ERROR MSG

enter image description here

What Range looks like

enter image description here

Upvotes: 1

Views: 652

Answers (1)

Alexander Bell
Alexander Bell

Reputation: 7918

The Error 2042 ("N/A") seems to be caused by the fact that the value returned by the Excel Worksheet function:

Aplication.WorksheetFunction.Max(Rnd(), 0.005)

which is always less than 1 will never get into specified range of values (>6) in column A. For testing purpose, try to substitute it with any number in that range of values in Column A, for example, sss0 =6.15 and modify the VLOOKUP() statement as following:

Debug.Print Application.VLookup(sss0, LookUp_Range, 2, 1)

(where 1 stands for logical TRUE) to get it working, i.e. finding the closest value (not exact match) as per your definition.

Hope this may help.

Upvotes: 1

Related Questions