Sign in to follow this  
Followers 0
Imbuter2000

Excel - how to test if a cell is #N/A from AutoIT?

12 posts in this topic

How do I test if the cell A2 in Excel is #N/A from AutoIT?

I tried the syntax "if $oE.Cells(1,2).Value == "#N/A" (or "N/A" or "") but it gives negative result.

note that msgbox(0,"",$oE.Cells(1,2).Value) shows empty value...

Share this post


Link to post
Share on other sites



"#N/A" is how Excel displays that there is no value for the specified cell. You want to compare the value of the cell.

Do a

msgbox(0,"",$oE.Cells(1,2).Formula)
for such a cell and see what you get.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

"#N/A" is how Excel displays that there is no value for the specified cell. You want to compare the value of the cell.

Do a

msgbox(0,"",$oE.Cells(1,2).Formula)
for such a cell and see what you get.

there is a complex vlookup formula that searches for a data in a second sheet and doesn't find it.

It's different from a "" value, like for example the result of =if(A1=9;"";""), so how can we test it in a "if" in AutoIT?

Share this post


Link to post
Share on other sites

in other words what is the equivalent of the Excel formula "IF(ISNA(A1);.........." in AutoIT? I don't find an "ISNA" command in AutoIT...

Share this post


Link to post
Share on other sites

I will come up with an answer as soon as I'm in my office again - there's no Excel on my Ubuntu machine :oops:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I will come up with an answer as soon as I'm in my office again - there's no Excel on my Ubuntu machine :oops:

Great, thanks in advance!

Share this post


Link to post
Share on other sites

In A2 i have a vlookup formula - it returns N/A . Here is what yo can get:

ConsoleWrite($oExcel.Application.ActiveSheet.Range("A2").Text&@CRLF) ; you will get #N/A

ConsoleWrite($oExcel.Application.ActiveSheet.Range("A2").Value&@CRLF) ; you will get nothing -

""

ConsoleWrite($oExcel.Application.ActiveSheet.Range("A2").Formula&@CRLF) ; you will get your formula - or in my case - =VLOOKUP(C2,F2:H8,1,FALSE)

Share this post


Link to post
Share on other sites

To check if cell "A2" is displayed as "#N/A" use this piece of code:

If $oExcel.Activesheet.Range("A2").Text = "#NA" Then MsgBox(0, "", "#NA")
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

To explain it a bit:

$oExcel.Activesheet.Range("A2").Formula returns the formula of the cell

$oExcel.Activesheet.Range("A2").Value returns the result after the formula has been evaluated

$oExcel.Activesheet.Range("A2").Text returns the output as seen by the user after the value has been formatted according to the formating rules for this cell

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Wow, great! thanks a lot Juvigy and water!!!

Share this post


Link to post
Share on other sites

Just wanted to add additional translations I just tested for those interested:

$Range = $oExcel.Application.Activesheet.Range("A2").Value

MsgBox(0,"Is the value of cell A2 a #N/A error?", $oExcel.Application.ISNA($Range))

You can then test (True or False) if the value of A2 is a true Excel N/A Error or not. Identical to =ISNA("Range").

Additionally,you can change the "ISNA" property to "ISERR" for other general Excel errors and to "ISTEXT" to test what is seen in the cell is text or not.

Share this post


Link to post
Share on other sites

Just wanted to add additional translations I just tested for those interested:

$Range = $oExcel.Application.Activesheet.Range("A2").Value

MsgBox(0,"Is the value of cell A2 a #N/A error?", $oExcel.Application.ISNA($Range))

You can then test (True or False) if the value of A2 is a true Excel N/A Error or not. Identical to =ISNA("Range").

Additionally,you can change the "ISNA" property to "ISERR" for other general Excel errors and to "ISTEXT" to test what is seen in the cell is text or not.

Fantastic, perfect now, thanks!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0