How do I code a VBA Excel 'OR' statement to include a test for #N/A cells ?

Hi All,

I was wondering if someone can give me the correct syntax to test a cell as either null, or containing a #N/A.
I've tried numerous variations but I get a compilation error each time. My code is...

If wsAD.Range("V" & i).Value = "" Or
    WorksheetFunction.IsNA(wsAD.Range("V" & i).Value) Then
          wsAD.Range("V" & i).Value = Application.VLookup(wsAD.Range("A" & i).Value,    LookupRange, 8, False)
          wsAD.Range("V" & i).Font.Bold = True
End If

Thanks in anticipation
Toco
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi Toco,

Have you tried using IsEmpty(...)?

  If WorksheetFunction.IsNA(wsAD.Range("V" & i).Value) Or _
     IsEmpty(wsAD.Range("V" & i)) Then
     wsAD.Range("V" & i).Value = Application.VLookup(wsAD.Range("A" & i).Value, LookupRange, 8, False)
     wsAD.Range("V" & i).Font.Bold = True
  End If

Open in new window


BFN,

fp.
0
FamousMortimerCommented:
Good Morning,

The value of an error cannot be converted to a string, try using .Text instead

If wsAD.Range("V" & i).Text = "" Or _
    WorksheetFunction.IsNA(wsAD.Range("V" & i).Value) Then
          wsAD.Range("V" & i).Value = Application.VLookup(wsAD.Range("A" & i).Value, LookupRange, 8, False)
          wsAD.Range("V" & i).Font.Bold = True

Open in new window


-FM
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Hi FM,

That worked just fine thank you very much.

Cheers
Toco
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

FamousMortimerCommented:
Thanks Toco,

Had you tried fanpages solution?  His/hers should have worked also.

If it did, the points should have at least been split.
0
[ fanpages ]IT Services ConsultantCommented:
Thanks FM; please don't worry about it.

Those that ignore my responses to their questions are treated accordingly whenever I see future questions.
0
TocogroupAuthor Commented:
Apologies for that fanpages. I mistakenly assumed FM's reply was partly as a response to your use of 'value'. Hence, I took his code and it worked.

I'm quite happy to split the points accordingly.

Sorry for the misunderstanding
Toco
0
[ fanpages ]IT Services ConsultantCommented:
:)

That's OK.  These things happen.  Lets just move on.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.