Jump to content

Recommended Posts

Posted

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

Posted

"#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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

"#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?

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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)

Posted

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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.

Posted

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!

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...