VLookUp picks some values in list, but not others

[Rx_]

Problem: VLookup Drop-Down list box shows all values. But, the associated lookup for VLookup works for some values, but not for others.

This code uses an Excel drop-down list based on the first column of a Named Range
The cells to the right of the drop-down list lookup up columns 5 to 24 in the Named Range and basically show the row.
This code puts the formula in the Excel Workbook from Access code via remote automation. This way, the end usercan select from the drop-down listbox, and the associated row shows up in the cells to the right.

3660    objxl.Range("E4").Select
3670          objxl.ActiveCell.FormulaR1C1 = "=VLOOKUP(Drop_Down,Matrix_Values,4)" ' Offset at 5 R_58 ' 
3680          For MyCounter = 5 To 24
3690              objxl.ActiveCell.Offset(0, 1).Range("A1").Select
3700              objxl.ActiveCell.FormulaR1C1 = "=VLOOKUP(Drop_Down,Matrix_Values, " & MyCounter & ")"
3710          Next MyCounter

 

[Rx_]

discovered that Microsoft tries to help us.
If the list of data VLookUP is running against isn't Sorted.... then it causes this inconsistant problem. VLOOKUP is lazy and stops looking once it passes an alpha logic sorted state.

VLOOKUP has a 4th (optional) parameter which if missing defaults to
TRUE (or 1) meaning that the lookup table needs to be sorted.
Solution:
If 4th Parameter is set to FALSE (or 0) then the table does not need to be
sorted, and VLOOKUP looks for an exact match in all the data.

The working code was updated and posted below:

3660    objxl.Range("E4").Select
3670          objxl.ActiveCell.FormulaR1C1 = "=VLOOKUP(Drop_Down,Matrix_Values,4,0)" ' Offset at 5 R_58 ' add 4th parmater 0 or data must be in alpha order
3680          For MyCounter = 5 To 24
3690              objxl.ActiveCell.Offset(0, 1).Range("A1").Select
3700              objxl.ActiveCell.FormulaR1C1 = "=VLOOKUP(Drop_Down,Matrix_Values, " & MyCounter & ",0)"
3710          Next MyCounter