Return value if no match found in Vlookup

[sakthi santhanam]

Hi,

I am stumbling in between my work...

wom = Application.WorksheetFunction.Match(vl6.Value, Sheet17.Range("A2:A" & Sheet17.UsedRange.Rows.Count), 0)
  If wom Is Nothing Then
  Sheet11.Range("I" & vl6.Row).Value = "Not Assigned"
  Else
  Sheet11.Range("I" & vl6.Row).Value = Application.WorksheetFunction.VLookup(vl6, bsr, 2, 0)
  End If

Here in the code, If I don't get the match then "Not Assigned" should be entered and if matches then the vlookup should give the respective value.

Is there any alternative way to find the vlookup if not match with the range.

Regards,
sakthi

 

[Colin Legg]

If you want to do this using Match and VLookUp then, with wom declared as a Variant type, you could do this...

  wom = Application.Match(vl6.Value, Sheet1.Range("A2:A" & Sheet1.UsedRange.Rows.Count), 0)
 
  If IsError(wom) Then
    Sheet11.Range("I" & vl6.Row).Value = "Not Assigned"
 
  'etc....

If you specifically want to use Application.WorksheetFunction.Match() then it will throw a runtime error if a match isn't found, so for that you would need to use VBA error handling.