PDA

View Full Version : Solved: Vlookup returning all but one value??



NateW
10-16-2007, 05:02 AM
Hi there...back again with yet another Vlookup kicking my butt. :)

The situation this time is that I've got the Vlookup working the way it is supposed to, but it omits one value every time...

Here is the code specific to the vlookup:


Sheets("Gate Control").Cells(gateRow, 9).Value = WorksheetFunction.VLookup(Workbooks(varWorkbook).Sheets("Liberty").Cells(varRow, 3), _
Sheets("Vlookup_Table").Range("A2:C10"), 3, False) ' Performs the Vlookup for WMS SKU's

On Error Resume Next 'Does error handling for Vlookup
If Error.Number = 1004 Then
Err.Clear
Else
MsgBox "There is an Error, call Nate"
Exit Sub
End If



The lookup table is attached (I just made a new worksheet with the data, don't worry about the cell references). The only one that is NOT showing up is:

EXCEL - L Exel EXEL - LUSA cdn funds-STD-DRY

Since everything else is working fine, I've got to assume it's something small I'm missing...but I've got no idea where to start to figure this out...

Please help!!

Thanks!!

Nate. :wot

Paul_Hossler
10-16-2007, 05:18 AM
This works, but I'd double check that the data in the cell -- Workbooks(varWorkbook).Sheets("Liberty").Cells(varRow, 3) -- that you think contains "EXCEL - L" really does, and that you don't have 2 spaces where you think you have only 1 (I learned that the hard way)

"Program's fine, must be the data"

Paul


Sub Test1()
Dim x As Variant
Dim s As String

s = "EXCEL - L"
x = WorksheetFunction.VLookup(s, Worksheets("Vlookup_Table").Range("$A:$C"), 3, False)
MsgBox x
End Sub

NateW
10-16-2007, 05:34 AM
Of course I would over-think this one...good grief, thanks so much - it was a difference in the data - EXEL vs. EXCEL...

Should have known, the data came from a compliance manager...hehe..

Thanks very much!