Hi all,

Im in a situation to capture an error with details as follows:
The user may import some data and then the macro will look up some
values according to the import (VLOOKUP function). If the import data
is not found, the default error value '#N/A' will be returned. In this
scenario, the user will get a warning message. But how can this error
be captured?
I tried
If xxx.value = "#N/A" Then //<Runtime error '13' - Type
Mismatch fired here
<aler msgbox
(I also checked the value of xxx in the immediate window using '?xxx',
which gives 'Error 2042')

Then I tried
If IsNA(xxx.value) Then //<Compile error: Sub or Function
not defined fired here
(Also noticed that VBA automatically modified 'ISNA'(my original
typing) to 'IsNA'. Why is this???)

Im also quite confused by 'End' and 'Exit Sub', i.e. what difference
between the following:

If Error Then
<error Handling
End
End If

If Error Then
<error Handling
Exit Sub
End if

Thanks in advance and regards
Frank

if xxx.text = "#N/A" then
...
would work

But so would:
if iserror(xxx.value) then
...


End on a line by itself is usually a very bad thing to use--I can't think of a
good reason to use it. It resets any persistent variables (public and static
variables).


OrientalPearl wrote:

Hi all,

Im in a situation to capture an error with details as follows:
The user may import some data and then the macro will look up some
values according to the import (VLOOKUP function). If the import data
is not found, the default error value '#N/A' will be returned. In this
scenario, the user will get a warning message. But how can this error
be captured?
I tried
If xxx.value = "#N/A" Then //<Runtime error '13' - Type
Mismatch fired here
<aler msgbox
(I also checked the value of xxx in the immediate window using '?xxx',
which gives 'Error 2042')

Then I tried
If IsNA(xxx.value) Then //<Compile error: Sub or Function
not defined fired here
(Also noticed that VBA automatically modified 'ISNA'(my original
typing) to 'IsNA'. Why is this???)

Im also quite confused by 'End' and 'Exit Sub', i.e. what difference
between the following:

If Error Then
<error Handling
End
End If

If Error Then
<error Handling
Exit Sub
End if

Thanks in advance and regards
Frank

--

Dave Peterson

Thanks Dave for your prompt help! The two solutions both work well. Re
iserror, I looked up ISNA() and it seems more specialised on working
with '#N/A'. But why doesn't it in this case? Also, '.Text' and
'.Value' do things diffferently? (apparently they make the difference
here)

Ok. I will stick to 'Exit Sub' then. Save a bit of effort in choosing
from the two ;-)

You help is highly appreciated!! Thanks!

OrientalPearl,
I think you'll find ISNA is an Excel worksheet function. As such VBA does
not understand what it means, hence the compile error.
If you want to use it, you need Application.WorksheetFunctions.ISNA(....

NickHK

"OrientalPearl" wrote in message
ups.com...
Thanks Dave for your prompt help! The two solutions both work well. Re
iserror, I looked up ISNA() and it seems more specialised on working
with '#N/A'. But why doesn't it in this case? Also, '.Text' and
'.Value' do things diffferently? (apparently they make the difference
here)

Ok. I will stick to 'Exit Sub' then. Save a bit of effort in choosing
from the two ;-)

You help is highly appreciated!! Thanks!

and .text shows what's in the cell--it's affected by numberformat, too.

1234.5332 could be the value, but $1,234.53 could be the .text.

OrientalPearl wrote:

Thanks Dave for your prompt help! The two solutions both work well. Re
iserror, I looked up ISNA() and it seems more specialised on working
with '#N/A'. But why doesn't it in this case? Also, '.Text' and
'.Value' do things diffferently? (apparently they make the difference
here)

Ok. I will stick to 'Exit Sub' then. Save a bit of effort in choosing
from the two ;-)

You help is highly appreciated!! Thanks!

--

Dave Peterson

Thanks Nick. I intend to blame Excel Help since it categorises both
ISNA and ISERROR functions together, however does not mention at all
the difference in their usage as seen from my example!

Regards
Frank

I see. Thanks Dave =)