Vlookup and #N/A
 

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

Vlookup and #N/A
 

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

Vlookup and #N/A
 

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 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

--

Dave Peterson

Vlookup and #N/A
 

On Feb 12, 3:57 pm, Dave Peterson wrote:
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 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

--

Dave Peterson

Obsolent/Dave Peterson,

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

Best regards,
Bob