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

[Imbuter2000]

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

[water]

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

[Imbuter2000]

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

[Imbuter2000]

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

[water]

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

[Imbuter2000]

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

Great, thanks in advance!

[Juvigy]

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)

[water]

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

[water]

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

[Imbuter2000]

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

[Reg2Post]

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.

[Imbuter2000]

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!