Страницы: 1
RSS
Получить номер строки с ошибкой #Н/Д
 
Есть большой лист, в котором с помощью функции ВПР() собирается информацию из других листов. Очень часто появляются ссылки на уже несуществующие значения, и как итог - появляется ошибка #Н/Д, которую хотелось бы обрабатывать. Для этого в самом верху пустая ячейка со "статусом", в которой хочется видеть номер строки с ошибкой (если есть), и в идеале - красить её для привлечения внимания в красный.

Проблема в том, что нужно получить номер строки с ошибкой #Н/Д из диапазона ячеек, а функции вроде ЕНД(), ЕОШИБКА() с диапазонами не работают.

Но все же можно как-нибудь?
 
Если Вы хотите чтобы вместо ошибки выводилась пустая строка или слово "Ошибка", используйте функцию =ЕСЛИОШИБКА(). При необходимости по ее результатам можно фильтровать список  
 
А если такая ошибка не одна на листе? Номер какой строки будет в ячейке со "статусом"? А красить, для привлечения внимания можно сами ячейки с ошибкой через Условное форматирование
Согласие есть продукт при полном непротивлении сторон
 
Я хочу взглянув на ячейку в самом верху понять - есть ли в данный момент ошибки #Н/Д, и если есть - на какой строке
 
Обрабатывайте ошибки сразу в Ваших формулах, например, как предложил MBT
Согласие есть продукт при полном непротивлении сторон
 
Их очень и очень много, плюс они действительно длинные что бы туда еще включить и обработку ошибок :cry:
Неужели совсем без этого не обойтись никак?
 
Тогда так
Код
Function EE(Rng As Range) As Long
For Each Cell In Rng
If Cell.Text = "#Н/Д" Then
    EE = Cell.Row
    Exit Function
End If
Next Cell
End Function

 
Вот и про "очень и очень много". Номер строки какой ячейки с ошибкой из этих "очень и очень", по Вашему мнению, должен быть в ячейке со статусом?
Согласие есть продукт при полном непротивлении сторон
 
Sanja, первая нашедшаяся строка с ошибкой, а после того как будет исправлена - то номер следующей

Спасибо MBT, попробую!
Изменено: Евгений - 02.04.2015 00:09:48
 
Немного модернизировал: теперь можно задавать номер вхождения ошибочной строки (необязательный аргумент)
Код
Function EE(ByVal Rng As Range, Optional Count As Long = 1) As Long
For Each Cell In Rng
If Cell.Text = "#Н/Д" Then
    If Count = 1 Then
        EE = Cell.Row
        Exit Function
     Else:
     Count = Count - 1
     End If
End If
Next Cell
End Function
Кстати, если заменить  If Cell.Text = "#Н/Д" на If IsError(Cell) функция будет работать с любыми ошибками
Изменено: МВТ - 02.04.2015 00:08:51 (Изменил проверку)
 
Цитата
собирается информацию из других листов
Так может собирать информацию при помощи макроса и в нем анализировать данные,
не нашел - красить ячейку или писать мессидж "Нет такого значения"
 
MBT, оба макроса при запуске выдают "Argument not optional"
 
Это не обычные макросы, это UDF
Согласие есть продукт при полном непротивлении сторон
 
Евгений, странно, у меня работают. Тем более, в первой UDF нет аргументов по умолчанию вообще :). Прикладываю файл с вставленной функцией. Кстати, если ошибок меньше, чем задан номер вхождения, то функция вернет 0.
Но, если Вы настаиваете на макросе, пожалуйста, он выведет в колонки J и K номера ВСЕХ строк с ошибками в выделенном диапазоне и соответствующие им ошибки. При этом, если выделено более одной колонки, выделение уменьшается до крайне левой.
Код
Sub EEE()
Dim L As Long
L = 1
With Selection
    If .Rows.Count > 1 Then .Resize(.Rows.Count, 1).Select
End With
For Each Cell In Selection
    If IsError(Cell) Then
      Cells(L, 10) = Cell.Row
      Cells(L, 11) = Cell.Text
      L = L + 1
    End If
Next Cell
End Sub

Изменено: МВТ - 02.04.2015 00:38:03 (Файл перезалил)
 
МВТ, Теперь возвращает и всё работает - спасибо за помощь!
 
странно. У меня сработала обычная формула (массивная):
Код
=МИН(ЕСЛИ(ЕНД($A$1:$A$20);СТРОКА($A$1:$A$20)))
F1 творит чудеса
 
По просьбе ТС немного видоизменил код: он будет создавать список гиперссылок на ячейки, содержащие ошибки. Если выделен в качестве диапазона проверки весь столбец, из него будут браться ячейки до последней заполненной.
Код
Sub EEE()
Dim L, L1, Cl As Long, HText As String
L = 1
Cl = 10
HText = """#" & ActiveSheet.Name & "!"
With Selection
    If .Columns.Count > 1 Then .Resize(.Rows.Count, 1).Select
    L1 = Cells(Rows.Count, .Column).End(xlUp).Row
    If .Rows.Count > L1 Then .Resize(L1, 1).Select
End With
For Each Cell In Selection
    If IsError(Cell) Then
      Cells(L, Cl).FormulaLocal = "=ГИПЕРССЫЛКА(" & HText & Cell.Address & """" & ";" & """" & "Строка " & Cell.Row & " Ошибка " & Cell.Text & """" & ")"
      L = L + 1
    End If
Next Cell
Cells(1, Cl).Select
End Sub


 
МВТ, Вы не могли бы немного модифицировать макрос из поста #10, просто добавив гиперссылку туда? Спасибо!
 
Чтобы функция возвращала гиперссылку у меня, к сожалению не получается (возможно как-то можно, но я не нашел - как). Но переделал формулу, чтобы она выводила результат, который можно вставлять в функцию рабочего листа =ГИПЕРССЫЛКА(EE(A:A))
Код
Function EE(ByVal Rng As Range, Optional Count As Long = 1) As String
Dim HText As String
HText = "#" & ActiveSheet.Name & "!"
With Rng
    If .Columns.Count > 1 Then .Resize(.Rows.Count, 1).Select
    L1 = Cells(Rows.Count, .Column).End(xlUp).Row
    If .Rows.Count > L1 Then .Resize(L1, 1).Select
End With
For Each Cell In Rng
If IsError(Cell) Then
    If Count = 1 Then
        EE = HText & Cell.Address & ""
        Exit Function
     Else:
     Count = Count - 1
     End If
End If
Next Cell
End Function

 
МВТ, Тоже вполне себе выход :) Но вы проверяли, это работает? Потому что у меня ругается на "Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен". В функцию рабочего листа вставляю абсолютно так же
 
Евгений,покажите, как Вы это делаете?
 
МВТ, Примерно так же как в примере у Вас, но с уточнением диапазона: =ГИПЕРССЫЛКА(EE(F$6:F$3100))

Кстати, странная вещь. Проверил сейчас, макрос всегда не работает в том случае, если диапазон ячеек отличен от А:А
 
На будущее, может кому понадобится - как я все же добился гиперссылки на строку с ошибкой. Использовался макрос МВТ из коммента №10, за что ему большое спасибо.

Ячейка с вызовом макроса выглядит так
Код
=ЕСЛИ(EE(E5:E3100)=0;" ";ГИПЕРССЫЛКА("[Книга Учета.xlsm]Лист!" & "A" & EE(E5:E3100);EE(E5:E3100)))

Сначала идет поиск ошибок в указанном диапазоне ячеек, если макрос возвращает (0), то их нет и оставляем ячейку пустой. Если есть, показываем номер строки и делаем её гиперсылкой.

Всем спасибо!
 
Jake, всегда пожалуйста :)
Евгений, я проверял на простеньком примере, вроде работало (я так понимаю, Вы говорите о функции в посте № 19?). Если приложите свой файл со вставленной функцией, возможно смогу сказать, где ошибка
 
На досуге доработал макрос - вдруг кому-от понадобится? Фактически, если есть необходимость искать ошибки вычислений именно таким образом, можно попробовать использовать его как надстройку
Код
'Позволяет выделять диапазон как на активном рабочем листе, так и не дюбом другом
'При наличии на активном листе гиперссылок, выдает запрос на их удаление
'Вставляет ссылку на первую найденную в выделенном диапазоне ошибку в
'ячейку, которая была активной на момент вызова макроса.
'Выводит окно с запросом на поиск следующей ошибки. В случае согласия выводит ссылку на нее
'в той же колонке на строку ниже
'С уважением, МВТ

Dim HText As String, CurCell As Range
Dim Rng
Set CurCell = ActiveCell
With ActiveSheet
If .Hyperlinks.Count <> 0 Then
If MsgBox("На рабочем листе есть гиперссылки (" _
& .Hyperlinks.Count & "). Удалить?", vbYesNo) = vbYes Then
For Each HL In .Hyperlinks
HL.Parent.Clear
Next HL
End If
End If
End With
On Error Resume Next
Set Rng = Application.InputBox("Укажите диапазон для очистки ячеек:", "Запрос данных", , Type:=8)
If Rng Is Nothing Then Exit Sub
With Rng
    If .Columns.Count > 1 Then .Resize(.Rows.Count, 1).Select
    L1 = Cells(Rows.Count, .Column).End(xlUp).Row
    If .Rows.Count > L1 Then .Resize(L1, 1).Select
End With
HText = "#" & Rng.Worksheet.Name & "!"
For Each Cell In Rng
If IsError(Cell) Then
    HText = HText & Cell.Address & ""
    ActiveSheet.Hyperlinks.Add Anchor:=Range(CurCell.Address), _
                               Address:=HText, _
                               TextToDisplay:="Строка: " & Cell.Row
    If MsgBox("Найти следующую ошибку?", vbYesNo) = vbNo Then Exit Sub
    Set CurCell = CurCell.Offset(1, 0)
End If
Next Cell
End Sub
Страницы: 1
Наверх