VLOOKUP error codes in VB

[dedmonds]

Hello can anyone suggest a way of stopping the VB program crashing if a VLOOKUP can't find a result?
I imagine it would be a LOOP UNTIL but I am not sure how to code it.

Cheers

Dave

[Aflatoon]

You may either use an error handler, or use a Variant to store the result - and use Application.Vlookup rather than Application.WorksheetFunction.Vlookup - and check the result using Iserror - for example:

Dim varResult as Variant
varresult = application.vlookup("blah", Range("range_name"), 2, false)
If IsError(varresult) then
msgbox "Not found"
Else
Call somemacrohere
End If

[justdriving]

How can WorkSheetFunction be used in above program?

[Aflatoon]

Yes, but then you need an error handler (can just be On Error Resume Next as long as you reset promptly thereafter).

[alexxgalaxy]

I always came up with erros using Worksheetfunction with relative reference. I tried different ways to correct the code but they were all failed attempts.

How to change Line 2's syntax if I want to use Worksheetfunction or Evaluate? Thanks.

Line 1 Range("D4", Range("D65536").End(xlUp)).Offset(0, 1).Select
Line 2 Selection.Formula = "=LEFT(RC[-1],FIND(""/"",RC[-1])-1)"

[Bob Phillips]

With Range("D4", Range("D65536").End(xlUp)).Offset(0, 1)

.Formula = "=LEFT(RC[-1],FIND(""/"",RC[-1])-1)"
.Value = .Value
End With

[alexxgalaxy]

With Range("D4", Range("D65536").End(xlUp)).Offset(0, 1)

.Formula = "=LEFT(RC[-1],FIND(""/"",RC[-1])-1)"
.Value = .Value
End With

Thank you for the quick reply but if there is no other way to use relative reference in Worksheetfunction or Evaluate? The only way I didn't come up with errors before was to name the region of the lookup value beforehand but somehow it returned nothing in the cells.

[Bob Phillips]

I am not understanding what your problem is/what you are trying to do.

[alexxgalaxy]

Okay, going back to my original question. Other than the code you suggested, what would the codes be if I want to use Worksheetfunction or Evaluate in Line 2 instead? Thanks.

Line 1 Range("D4", Range("D65536").End(xlUp)).Offset(0, 1).Select
Line 2 Selection.Formula = "=LEFT(RC[-1],FIND(""/"",RC[-1])-1)"

[Bob Phillips]

Why you you WANT to use WorksheetFunction in VBA when it is not necessary? It involves a round-trip to Excel which is highly inefficient.

[alexxgalaxy]

Why you you WANT to use WorksheetFunction in VBA when it is not necessary? It involves a round-trip to Excel which is highly inefficient.

I was trying to learn more about the syntax of WorksheetFunction. The materials I've read before always covered so little about it. Anyway, I tried the codes you suggested and came up with a problem. One of the results of the formula "LEFT(RC[-1],FIND(""/"",RC[-1])-1)" was "002211" but it became 2211 with the code ".value". How can I keep the original value? Thanks.