How can I find the next cell with an error with 'find and replace' as well as in a macro?

[htodd]

I frequently download a spreadsheet from a 3rd party. It is filled with errors. Mostly the IFERROR function is missing the ‘then’ statement (apparently Google Sheets and Excel do not have an issue with this) but it generates an Err:511 in Calc.

I reached out to the third party and they are not interested in support Calc.

I would like to be able to manually find the errors.

Also, I am looking into writing macros that would automatically detect the error and add the appropriate ‘then’ condition based on cell format type e.g. ‘, “”’ for text fields and ‘, 0’ for numeric fields. How can I find the next field with a Err:511 via the find function in a macro?

[erAck]

@htodd

Mostly the IFERROR function is missing an else statement (apparently Google Sheets and Excel do not have an issue with this) but it generates an Err:511 in Calc.

IFERROR() has no else statement, only a then statement, the second argument, the alternative value to use if an error occurred in the first argument.

However, out of interest: what do both Excel and GSheets do in the case an error is caught and there is no alternative value? The MS IFERROR function documentation says both arguments are required.

[htodd]

Sorry for my misstatement. I edited my question substituting ‘then’ for ‘else’ You are correct – but you already knew that!

I don’t have access to Excel, but in Google sheets an error apparently generates an “empty” cell – at least the fields had no displayed value for both text and numeric formatted fields. The spreadsheet author “supports” Excel so I assume the same happens there – or at least no error is generated for the missing “then” parameter.

Here is an actual example formula from Google sheets that doesn’t generate an error: =IFERROR(VLOOKUP($B3,industry_table,3,FALSE))

[Wanderer]

Then IFERROR has no second term here.

Can you provide a small sample from your Excel -file to check with Excel.

Btw: wich version of LO do you use?

[mikekaganski]

No, MS Excel does not accept a single-argument IFERROR; it even doesn’t allow to type such a formula:

@htodd

in Google sheets an error apparently generates an “empty” cell – at least the fields had no displayed value for both text and numeric formatted fields. The spreadsheet author “supports” Excel so I assume the same happens there – or at least no error is generated for the missing “then” parameter.

Google Sheets do quite a few incompatible changes in their program; their “support” means they can read the documents from MS Excel, but nothing guarantees that Google-generated documents would work in MS Excel the same way.

[mikekaganski]

IfError_1arg - Google Tabellen is a Goodle Sheets document with a single =iferror(1/0) formula in A1. You may download it as Excel XLSX from there; and this is what Excel shows trying to open it:

[JohnSUN]

@htodd

So grab a copy if you want to play.

I took a copy, played a little. I think this trick will help you save some time:

After that, there will still be 1911 errors - ErrList of Investment Decision Bot All Tickers.ods (41.1 KB)

[htodd]

Interesting. So much for supporting Excel!

[htodd]

Awesome!
Thanks!

[htodd]

Apparently, I get to answer my own question…

Type: <CTRL+F> type: Err:511 and check “Formatted Display” then click the down arrow.

I recorded the above steps in a macro and it produced this:
REM ***** BASIC *****

sub Find_Err511
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)

rem ----------------------------------------------------------------------
dim args1(20) as new com.sun.star.beans.PropertyValue
args1(0).Name = “SearchItem.StyleFamily”
args1(0).Value = 2
args1(1).Name = “SearchItem.CellType”
args1(1).Value = 1
args1(2).Name = “SearchItem.RowDirection”
args1(2).Value = true
args1(3).Name = “SearchItem.AllTables”
args1(3).Value = false
args1(4).Name = “SearchItem.SearchFiltered”
args1(4).Value = false
args1(5).Name = “SearchItem.Backward”
args1(5).Value = false
args1(6).Name = “SearchItem.Pattern”
args1(6).Value = false
args1(7).Name = “SearchItem.Content”
args1(7).Value = false
args1(8).Name = “SearchItem.AsianOptions”
args1(8).Value = false
args1(9).Name = “SearchItem.AlgorithmType”
args1(9).Value = 0
args1(10).Name = “SearchItem.SearchFlags”
args1(10).Value = 0
args1(11).Name = “SearchItem.SearchString”
args1(11).Value = “Err:511”
args1(12).Name = “SearchItem.ReplaceString”
args1(12).Value = “”
args1(13).Name = “SearchItem.Locale”
args1(13).Value = 255
args1(14).Name = “SearchItem.ChangedChars”
args1(14).Value = 2
args1(15).Name = “SearchItem.DeletedChars”
args1(15).Value = 2
args1(16).Name = “SearchItem.InsertedChars”
args1(16).Value = 2
args1(17).Name = “SearchItem.TransliterateFlags”
args1(17).Value = 256
args1(18).Name = “SearchItem.Command”
args1(18).Value = 0
args1(19).Name = “SearchItem.SearchFormatted”
args1(19).Value = true
args1(20).Name = “SearchItem.AlgorithmType2”
args1(20).Value = 1

dispatcher.executeDispatch(document, “.uno:ExecuteSearch”, “”, 0, args1())

end sub

[JohnSUN]

@htodd Yes, this is an acceptable solution - at least it solves the problem. You may be interested in taking a look at Bugerra - the description says that it is designed specifically for your task.

[Villeroy]

https://extensions.libreoffice.org/en/extensions/show/1983