Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Vlookup returning wrong value

Posted on 2012-09-03
8
Medium Priority
?
473 Views
Last Modified: 2012-09-03
Hello,

The following code is used to look up a corresponding value in a look up table as I loop through files in a folder.  I can retrieve some files but not all.  I've checked the lookup table ("WOS") and the corresponding values are there.  It seems that  vLookupCheck = Application.VLookup(strBU, ThisWorkbook.Worksheets("Upload").Range("WOS"), 1) is only working some of the times

 vLookupCheck = Application.VLookup(strBU, ThisWorkbook.Worksheets("Upload").Range("WOS"), 1)
                If Not IsError(vLookupCheck) Then
                Debug.Print Left(varMyFile.Name, 5)
                    If Left(varMyFile.Name, 5) = vLookupCheck Then
                        Set wbs = Workbooks.Open(FileName:=varMyFile)
                        Call Utilities.DeleteRangeNames(wbs)
                        Call PopulateExchangeRate(wbs)
                        If Workbooks(varMyFile.Name) Is Nothing Then
                            Workbooks.Open FileName:=varMyFile
                        End If
                        CopySelectedWorksheets (varMyFile.Name)
                        Application.DisplayAlerts = False
                        Excel.Workbooks(varMyFile.Name).Close (True)
                        Application.DisplayAlerts = True
                    End If

Open in new window

0
Comment
Question by:chtullu135
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 38361712
Is the dara in the WOS table definiately sorted correctly?
0
 

Author Comment

by:chtullu135
ID: 38361717
The values in the look up table are formatted as text values and are unique.  They are not sorted though
0
 

Author Comment

by:chtullu135
ID: 38361747
I manually resorted the values and it worked.  However, I want users to be able to enter a new value into the lookup table without having to worry about manually resorting the values.  What if I add another column to the lookup table as a sort column.  When the user adds a new value to the lookup table the sort column would be incremented by 1
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Accepted Solution

by:
barry houdini earned 1600 total points
ID: 38361774
In this:

vLookupCheck = Application.VLookup(strBU, ThisWorkbook.Worksheets("Upload").Range("WOS"), 1)

You have no 4th Argument so the Default TRUE is used which means the lookup range needs to be sorted (and you might get a "nearest match". You don't want either of those so add FALSE as 4th argument, then you don't need to sort and it should still work, i.e.

vLookupCheck = Application.VLookup(strBU, ThisWorkbook.Worksheets("Upload").Range("WOS"), 1,FALSE)

regards, barry
0
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 400 total points
ID: 38361775
In that case you need to change your line 1 to
vLookupCheck = Application.VLookup(strBU, ThisWorkbook.Worksheets("Upload").Range("WOS"), 1,False)

Open in new window

The 'False' tells VLOOKUP to do an exact match, which does not require the data to be sorted.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 38361777
Like Barry just said - must refresh more often...
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38361857
as you were only 19 seconds after me I would regard that as "simultaneous posting" :)

barry
0
 

Author Closing Comment

by:chtullu135
ID: 38362076
Thanks,  I went ahead and gave andrew 100 points for being beat by only 19 seconds
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question