Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню Заметки | Поиск ячеек, содержащих ошибки и работа над ошибками :)


Rambler's Top100


Counter CO.KZ

Предполагается, что :

  • необходимо найти все ячейки, содержащие значение ошибки и заменить их на формулу

    =ЕСЛИ(ЕОШИБКА(ПЕРВОНАЧ_ФОРМУЛА);"";ПЕРВОНАЧ_ФОРМУЛА)

    Обладатели же Microsoft Excel 2007(и старше) могут использовать более лаконичный вариант, но не стоит забывать, что в более ранних версиях, эта функция работать не будет (по причине её отсутствия)

    =ЕСЛИОШИБКА(ПЕРВОНАЧ_ФОРМУЛА;"")

  • активный рабочий лист не защищён
  • ячейки не содержат формул массива

    Вариант I.

    Если диапазон, который нам требуется обработать, относительно небольшой, то заменить формулы, которые возвращают ошибку, можно даже так :
  • Private Sub ReplaceFormulaError() 'XL95(и старше)
        Dim iCell As Range, iFormula$
        For Each iCell In [A1:C100]
            If IsError(iCell) = True Then
               iFormula = Mid(iCell.Formula, 2)
               iFormula = "=IF(ISERROR(" & iFormula & "),""""," & iFormula & ")"
               iCell.Formula = iFormula
            End If
        Next
    End Sub
    Особенности : Если в указанном диапазоне A1:C100 будет хотя бы одна ячейка, где ошибка это не результат вычислений формулы, а константа, то Вы получите ошибку, которую можно избежать, если использовать следующие варианты.

    Вариант II.
    Private Sub ReplaceFormulaAndValueError() 'XL95(и старше)
        Dim iCell As Range, iFormula$
        For Each iCell In [A1:C100]
            If IsError(iCell) = True Then
               iFormula = Mid(iCell.Formula, IIf(iCell.HasFormula, 2, 1))
               iFormula = "=IF(ISERROR(" & iFormula & "),""""," & iFormula & ")"
               iCell.Formula = iFormula
            End If
        Next
    End Sub
    Private Sub ReplaceFormulaAndValueError2v1() 'XL95(и старше)
        Dim iCell As Range, iFormula$
        For Each iCell In [A1:C100]
            If IsError(iCell) = True Then
               iFormula = iCell.Formula
               If iCell.HasFormula = True Then iFormula = Mid(iCell.Formula, 2)
               iFormula = "=IF(ISERROR(" & iFormula & "),""""," & iFormula & ")"
               iCell.Formula = iFormula
            End If
        Next
    End Sub
    Private Sub ReplaceFormulaAndValueError2v2() 'XL2000(и старше)
        Dim iCell As Range, iFormula$
        For Each iCell In [A1:C100]
            If IsError(iCell) = True Then
               iFormula = Replace(iCell.Formula, "=", "", , 1)
               iFormula = "=IF(ISERROR(" & iFormula & "),""""," & iFormula & ")"
               iCell.Formula = iFormula
            End If
        Next
    End Sub

    Вариант III.

    Если необходимо заменить только формулы, а константы оставить в покое, то :
    Private Sub ReplaceFormulaError2() 'XL95(и старше)
        Dim iSource As Range, iCell As Range, iFormula$
        
        On Error Resume Next
        Set iSource = [A1:C100].SpecialCells(xlFormulas, xlErrors)
        
        If Not iSource Is Nothing Then
           For Each iCell In iSource
               iFormula = Mid(iCell.Formula, 2)
               iFormula = "=IF(ISERROR(" & iFormula & "),""""," & iFormula & ")"
               iCell.Formula = iFormula
           Next
        End If
    End Sub
    Если же, наоборот, нужно заменить только константы, а формулы с ошибками, оставить, то :
    Private Sub ReplaceValueError() 'XL95(и старше)
        Dim iSource As Range, iCell As Range, iFormula$
        
        On Error Resume Next
        Set iSource = [A1:C100].SpecialCells(xlConstants, xlErrors)
        
        If Not iSource Is Nothing Then
           For Each iCell In iSource
               iFormula = iCell.Formula
               iFormula = "=IF(ISERROR(" & iFormula & "),""""," & iFormula & ")"
               iCell.Formula = iFormula
           Next
        End If
    End Sub
    Особенности :
  • Если Вы планируете искать ошибки во всех ячейках листа, то имейте ввиду, что при обработке больших диапазонов, имеет смысл отключать обновление экрана и устанавливать ручной пересчёт формул. А в случае наличия событий листа/книги/приложения, и блокировать их выполнение.
  • Кроме того, желательно учитывать, что метод SpecialCells может возвратить максимум 8192 несмежных ячеек/диапазонов.

    The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected in Excel

  • Если в качестве исходного диапазона Вы укажете одну единственную ячейку, например [A1].SpecialCells(xlConstants) , то поиск ячеек(ошибок) будет осуществляться во всём рабочем листе.

    Вариант IV.

    Пример, где замена формул/констант происходит в элементах массива, что с одной стороны, ускоряет время обработки, а с другой, требует значительных ресурсов.
  • Private Sub ReplaceFormulaAndValueError3() 'XL95-2003
        Dim iRow&, iColumn&, iFormula$, iArr1, iArr2
        
        iArr1 = ActiveSheet.UsedRange.Value
        iArr2 = ActiveSheet.UsedRange.Formula
        
        For iRow = 1 To UBound(iArr1)
            For iColumn = 1 To UBound(iArr1, 2)
                If IsError(iArr1(iRow, iColumn)) = True Then
                   iFormula = iArr2(iRow, iColumn)
                   If iFormula Like "=*" Then 'If Asc(iFormula) = 61 Then
                      iFormula = Mid(iFormula, 2)
                   End If
                   iFormula = "=IF(ISERROR(" & iFormula & "),""""," & iFormula & ")"
                   iArr2(iRow, iColumn) = iFormula
                End If
            Next
        Next
        
        ActiveSheet.UsedRange.Formula = iArr2
    End Sub
    Private Sub ReplaceFormulaAndValueError3v2() 'XL2000 (и старше)
        Dim iRow&, iColumn&, iFormula$, iArr1, iArr2
        
        iArr1 = ActiveSheet.UsedRange.Value
        iArr2 = ActiveSheet.UsedRange.Formula
        
        For iRow = 1 To UBound(iArr1)
            For iColumn = 1 To UBound(iArr1, 2)
                If IsError(iArr1(iRow, iColumn)) = True Then
                   iFormula = Replace(iArr2(iRow, iColumn), "=", "", , 1)
                   iFormula = "=IF(ISERROR(" & iFormula & "),""""," & iFormula & ")"
                   iArr2(iRow, iColumn) = iFormula
                End If
            Next
        Next
        
        ActiveSheet.UsedRange.Formula = iArr2
    End Sub
    Тоже самое, только вместо конкатенации(об'единения) новая формула создаётся с помощью замены.
    Private Sub ReplaceFormulaAndValueError3v3() 'XL2000 (и старше)
        Dim iRow&, iColumn&, iFormula$, tmp$, iArr1, iArr2
        
        iArr1 = ActiveSheet.UsedRange.Value
        iArr2 = ActiveSheet.UsedRange.Formula
        
        tmp = "=IF(ISERROR(ErrX),"""",ErrX)"
        
        For iRow = 1 To UBound(iArr1)
            For iColumn = 1 To UBound(iArr1, 2)
                If IsError(iArr1(iRow, iColumn)) = True Then
                   iFormula = Replace(iArr2(iRow, iColumn), "=", "", , 1)
                   iFormula = Replace(tmp, "ErrX", iFormula)
                   iArr2(iRow, iColumn) = iFormula
                End If
            Next
        Next
        
        ActiveSheet.UsedRange.Formula = iArr2
    End Sub

    Примечание : Как уже говорилось в самом начале заметки, обладатели MS Excel 2007(и старше) могут использовать свой вариант новой формулы, а именно :
    iCell.Formula = "=IFERROR(" & iFormula & ","""")" 


    Вопросы, которые связаны с этой темой
  • Как определить источник возникновения ошибки ?
  • Как скрыть, заменить или удалить значение ошибки, в т.ч. возвращаемое формулой ?

  • FAQ177 : Как определить содержит ли ячейка значение ошибки и как определить тип ошибки ?
  • FAQ554 : Как определить содержат ли ячейки диапазона значение ошибки ?
  • FAQ561 : Как найти все ячейки содержащие определённую ошибку, например, #Н/Д ?
  • FAQ712 : Как очистить содержимое всех ячеек, которые содержат определённую ошибку, например, #Н/Д ?




  • Автор : Климов Павел Юрьевич
    © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff