Vlookup and #N/A

[tiptoe]

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

 

[obsolent]

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)

 

[Dave Peterson]

Dim res as variant 'could be error
Sheets("Hidden").Select
Range("j1").Select
For n = 1 To NumRows - 1
ActiveCell.Offset(1, 0).Select

res = Application.VLookup(Range("i" & n + 1), _
Workbooks(mshare).Sheets("Sheet1").Range("A:Z"), 7, False)

if iserror(res) then
activecell.value = "No Match"
else
activecell.value = res
end if

Next n

 

[tiptoe]

Dim res as variant 'could be error
Sheets("Hidden").Select
Range("j1").Select
For n = 1 To NumRows - 1
ActiveCell.Offset(1, 0).Select

res = Application.VLookup(Range("i" & n + 1), _
Workbooks(mshare).Sheets("Sheet1").Range("A:Z"), 7, False)

if iserror(res) then
activecell.value = "No Match"
else
activecell.value = res
end if

Next n

Obsolent/Dave Peterson,

Many, many thanks - problem solved. Maybe one day I will get the hang
of this!

Best regards,
Bob