Quick Vlookup Theory Question

[NateW]

Hi.

If you do a vlookup, and no corresponding value is found, what would the value transferred be with the following code:



Sheets("Gate Control").Cells(varRow, 9).Value = WorksheetFunction.VLookup(Sheets("Gate Control").Cells(varRow, 9).Value, _
Sheets("Carrier_Lookup_Table").Range("C3:D1000"), 2, False)



The reason I ask is I'm basing some later logic on the fact that if it searches for a corresponding value to "Zion Transport", and finds nothing, then


Sheets("Gate Control").Cells(varRow, 9).Value =


should equal 0. At least, that's what I'm thinking.

Any thoughts?

[mikerickson]

From the Excel help sysetm "If an exact match is not found, the error value #N/A is returned."

specificaly, in your example, CVErr(xlErrNA) is what would be returned and put in the cell, hence the #/NA.

[herzberg]

Just to share my experience on this. If no value is found, a runtime error will result and the code execution stops. This can be pre-empted with some error handling code, though.

On the other hand, if you write the cell formula directly, e.g. Cell(1, 1).FormulaR1C1 = "=Vlookup(xx,yy,aa,vv)", even if there's no match, the code runs along just fine.