[Решено] Поиск ячеек с ошибками

[Sirius34]

Всем доброго времени суток!

Макросом необходимо найти строку, в которой определённая ячейка содержит ошибку #Н/Д (#N/A).
В VBA Excel это делается просто

Код Выделить Развернуть

   
RwEr = sht.Cells.Find(What:="#N/A", _
        After:=sht.Range(adrStrt), _
        LookAt:=xlPart, _
        LookIn:=xlValues, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False).Row

Почему-то в LO это не срабатывает.
Математическое значение ячейки с ошибкой #N/A равно 0, длина ячейки (LEN) выдаёт #N/A, т.е. простой перебор циклом не подходит.

Что можно придумать?

[mikekaganski]

Код Выделить Развернуть

sub SearchNA
  sheet = ThisComponent.getSheets().getByIndex(0)
  searchDesc = sheet.createSearchDescriptor
  searchDesc.setPropertyValue("SearchFormatted", true)
  searchDesc.setPropertyValue("SearchType", 1)
  searchDesc.setSearchString("#N/A")
  result = sheet.findFirst(searchDesc)
end sub

Здесь:

• "SearchFormatted" - недокументированное свойство дескриптора поиска Calc (соответствует чекбоксу "Formatted Display" в диалоге замены);
• "SearchType" - недокументированное свойство дескриптора поиска Calc (соответствует выпадающему списку Search In; возможные значения определены здесь: 0 - поиск в формулах, 1 - поиск в значениях; 2 - поиск в комментариях).

Код соответствующего объекта дескриптора здесь.

[Sirius34]

mikekaganski
Спасибо большое!

Только два момента:
1. Можно ли ограничить диапазон поиска столбцом или строкой?

2. В русской версии LO вот это не сработало
searchDesc.setSearchString("#N/A")
Пришлось #N/A менять на #Н/Д.
Так задумано?

[mikekaganski]

Можно ли ограничить диапазон поиска столбцом или строкой?

Я не пробовал, но моя первая мысль - исходить из того, что здесь используется интерфейс XSearchable, который реализован в т.ч. у сервиса SheetCellRange (то есть произвольного диапазона ячеек - в т.ч. у столбца / строки). Попробуйте использовать такой диапазон вместо всего листа в коде.

Пришлось #N/A менять на #Н/Д.
Так задумано?

Смотря что "задумано". Как я и написал,

"SearchFormatted" - недокументированное свойство дескриптора поиска Calc (соответствует чекбоксу "Formatted Display" в диалоге замены)

Этот чекбокс ищет в ячейках текст так, как он там показан. Так что да, это ожидаемо. А вот как сделать переносимо - не знаю.

[bigor]

Только два момента:

1. Поиск можно вести по диапазону.
2. Н/Д нужно при использовании русских имен функций.

[sokol92]

Альтернатива - метод queryFormulaCells с параметром com.sun.star.sheet.FormulaResult.ERROR.

За один вызов можно найти ошибки сразу во всем документе.

Далее анализируем ячейки результата. (Псевдо)свойство Error для ошибки  "#N/A" (#Н/Д) равно 32767.

Кстати: в Excel указанный в стартовом сообщении метод работать не будет, если ширины столбца недостаточно, чтобы показать полностью текст "#N/A" (независимо от кодовой страницы по умолчанию  ). Проверьте!

[Sirius34]

Кстати: в Excel указанный в стартовом сообщении метод работать не будет, если ширины столбца недостаточно, чтобы показать полностью текст "#N/A" (независимо от кодовой страницы по умолчанию  ). Проверьте!

Проверил с шириной столбца = 0,1 и со скрытым столбцом. У меня скрипт работает
Версия Excel - 2021, если это имеет значение.

[Sirius34]

В общем, пути решения моей изначальной проблемы есть, даже вариативны.
Дальше уже немного допилю под свои нюансы.

Спасибо всем за помощь!

[sokol92]

Проверил с шириной столбца = 0,1 и со скрытым столбцом. У меня скрипт работает

Это обман зрения.

Давайте Ваш тест запишем в такой редакции (VBA Excel):

Код Выделить Развернуть

Public Sub FndNA()
    Dim Sht As Object
    Dim AdrStrt
    Dim ColWidth
    Dim CellErr As Range
   
    Set Sht = ThisWorkbook.Sheets("Áàçà")
   
    AdrStrt = "J2"
   
    For Each ColWidth In Array(0, 0.1, 7)
   
        Range(AdrStrt).Select
        Selection.ColumnWidth = ColWidth
       
        Set CellErr = Sht.Cells.Find(What:="#N/A", _
                After:=Sht.Range(AdrStrt), _
                LookAt:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
       
        MsgBox "ColWidth = " & ColWidth & Chr(10) & "Error in the cell " & CellErr.Address
    Next ColWidth
End Sub
При узком столбце J ошибочные ячейки в нем не обнаруживаются.

См. также здесь.

[Sirius34]

При узком столбце J ошибочные ячейки в нем не обнаруживаются.

Проверил. И правда - обман зрения
Примитивнейший скрипт "не видит" искомые данные. Мда уж... Просто нет слов.
Теперь буду знать и учитывать (придётся дополнительную команду в код внести, чтобы
ширину столбца увеличивала перед поиском).
Спасибо за информацию!

[sokol92]

Кстати, вот семинар по теме применения метода Excel Range.Find c параметром LookIn:=xlValues.