My VLookup produces no matches. 3

[mscallisto]

My VLookup produces no matches.


I have a table that looks like this:

A       B
-------------------------
71100	GS EAST MGMT        
71140	GS EAST TECH SUPP   
71102	GS EAST NTWK/HD     
71103	GS EAST HELP DESK   
71104	GS EAST WEB APPS    
71105	GS EAST SAP DEV     
71110	GS EAST ITASCA DT   
71130	GS WEST MGMT

And data that looks this

A       B
-------------------------
71140 - Content Management Delivery
71156   GS Info Deliver/Bus Sys -
71110   Advanced Tech -APP Software

Sub CleanUpData()
Dim intLastRow As Integer
Dim usedrowsCol_B As Integer
Dim SearchItem As String
Dim Company As Range

intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
usedrowsCol_B = Worksheets("sheet2").Cells(Cells.Rows.Count, "B").End(xlUp).Row

Set Company = Worksheets("sheet2").Range("A1:B" & usedrowsCol_B)

    
For Ii = 1 To intLastRow 
    SearchItem = Mid(Trim(Cells(Ii, 2)), 1, 5)
    x = Application.VLookup(SearchItem, Company, 2, False)
    If IsError(x) Then
        x = "no match found"
     Else
        MsgBox ("match found")
    End If
etc.

My VLookup produces no matches.

Yet If I change the value 71110 to abcde in both the lookup table and the data the VLookup works great.

I have tried everything to ensure the table and data are the same type but aparently I'm missing something somewhere.

btw I added Company to the watch window and it shows the (1) value to be "variant double" and the (2) value to be "variant string"
while SearchItem is "String"

so I'm certain the No Match is because of a "type" issue yet I can't see where.

 

[mscallisto]

I've used VLookup many times and have never had this problem.

sam

 

[SkipVought]

Hi,

Are you ABSOLUTELY sure of EXACTLY what is in SearchItem???

Have you STEPPED thru your code in debug and checked EVERY relevant variable and object, using the Watch Window???

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[mscallisto]

when F8'ing thru the code, the watch window shows SearchItem to be exactly as I expect it to be.

and as I said, if I replace the value 71110 to abcde in both the lookup table and the data the VLookup works great

what am I missing??

 

[mintjulep]

Searchitem is dimed as a string.

Column A of your search range appears to contain numbers.

 

[SkipVought]

Yes, NUMBERS and STRING are different animals!!!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[mscallisto]

SearchItem = Mid(Trim(Cells(Ii, 2)), 1, 5) and was originally typed as variant.

So in this case SearchItem would be "71140"

B      
------------------------------------
71140 - Content Management Delivery
71156   GS Info Deliver/Bus Sys -
71110   Advanced Tech -APP Software

So am I supposed to convert this to numeric or make my table column 1 type "Text"

I'm not sure I know the best way to fix this.

 

[AnotherHiggins]

SearchItem = value(Mid(Trim(Cells(Ii, 2)), 1, 5))
[tab]Or
Format the column A as Text

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mscallisto]

Thanks 2 all

SearchItem = val(Mid(Trim(Cells(Ii, 2)), 1, 5))

worked