The result of a failing VLookup is an error, and you can test for it
i.e.
instead of
ActiveCell.Value = Application.VLookup(...................)
put
ans = Application.VLookup(Range("i" & n + 1..... ....)
ActiveCell.Value = IIf(VarType(ans) = vbError, "No Match", ans)



--
Shorter programs last longer!


"tiptoe" wrote:

Thanks to Dave Peterson for helping with an earlier request, I have
the following code:

Sheets("Hidden").Select
Range("j1").Select
For n = 1 To NumRows - 1
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Application.VLookup(Range("i" & n + 1),
_
Workbooks(mshare).Sheets("Sheet1").Range("$A:$Z"), 7,
False)
Next n

My question now is, when no match is found, how can I replace the
resulting #N/A report with a text statement, e.g. "No match" ?

Any suggestions would be appreciated,

Bob
Nottingham UK