Excel: vlookup and sorted data

[Malachy]

Hi,

I'm calling the vlookup function from VBA code, but I only get the correct values when the table is sorted. Is this right? I thought the vlookup function could look up unsorted tables for exact matches - indeed, if I use the vlookup as a formula in a worksheet cell it returns the correct value without the need for sorting!

Can anyone advise how to call the vlookup function from VBA without sorting the table first?

My code snippet is...

itemcost = Application.VLookup(priceCode, ratesDataBase, 7, True)
[\code]
I currently work around the problem by sorting the table first, and then sorting it back again...

[code]
'seems to require sorted data...
    Range("'[" + TenderFilename + "]RatesDatabase'!A" + CStr(FirstRowRatesDatabase - 1) + ":H" + CStr(FirstRowRatesDatabase + totalPriceCodes - 1)).Sort Key1:=Range("'[" + TenderFilename + "]RatesDatabase'!A" + CStr(FirstRowRatesDatabase)), Order1:=xlAscending, header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
           
'Get the data
    itemcost = Application.VLookup(priceCode, ratesDataBase, 7, True)
                
'sort back to how it was...
    Range("'[" + TenderFilename + "]RatesDatabase'!A" + CStr(FirstRowRatesDatabase - 1) + ":H" + CStr(FirstRowRatesDatabase + totalPriceCodes - 1)).Sort Key1:=Range("'[" + TenderFilename + "]RatesDatabase'!B" + CStr(FirstRowRatesDatabase)), Order1:=xlAscending, Key2:=Range("'[" + TenderFilename + "]RatesDatabase'!D" + CStr(FirstRowRatesDatabase)) _
        , Order2:=xlAscending, Key3:=Range("'[" + TenderFilename + "]RatesDatabase'!E" + CStr(FirstRowRatesDatabase)), Order3:=xlAscending, header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
[\code]    

But this is far from desirable.

Thank you for your help.

M.

 

[DaleWatson123321]

With your Lookup function, change the "True" to "False".

This should enable your function to work WITHOUT having to sort.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca