Imbuter2000 Posted March 30, 2012 Posted March 30, 2012 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 Posted March 30, 2012 Posted March 30, 2012 "#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
Imbuter2000 Posted April 1, 2012 Author Posted April 1, 2012 "#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 Posted April 1, 2012 Author Posted April 1, 2012 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 Posted April 1, 2012 Posted April 1, 2012 I will come up with an answer as soon as I'm in my office again - there's no Excel on my Ubuntu machine 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
Imbuter2000 Posted April 1, 2012 Author Posted April 1, 2012 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 Posted April 2, 2012 Posted April 2, 2012 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 Posted April 2, 2012 Posted April 2, 2012 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") 232showtime 1 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
water Posted April 2, 2012 Posted April 2, 2012 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 232showtime 1 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
Imbuter2000 Posted April 2, 2012 Author Posted April 2, 2012 Wow, great! thanks a lot Juvigy and water!!!
Reg2Post Posted April 2, 2012 Posted April 2, 2012 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 Posted April 3, 2012 Author Posted April 3, 2012 Just wanted to add additional translations I just tested for those interested:$Range = $oExcel.Application.Activesheet.Range("A2").ValueMsgBox(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!
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now