Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Макросы | Range (Cells)


Rambler's Top100


Counter CO.KZ


    [1] [2] [2 err] [3] [4] [5] [6]

  1. Как определить содержит ли ячейка значение ошибки и как определить тип ошибки ? 29.01.2007
  2. Как перебрать ячейки конкретного диапазона и проверить их на наличие ошибок ? 20.07.2014
  3. Как определить содержат ли ячейки диапазона значение ошибки ? 13.09.2010
  4. Как найти все ячейки содержащие определённую ошибку, например, #Н/Д ? 30.09.2010
  5. Как очистить содержимое всех ячеек, которые содержат определённую ошибку, например, #Н/Д ? 19.04.2015
  6. Как найти все ячейки, где содержатся ошибки и заменить их на формулу, возвращающую пустую строку ? 26.06.2016
    [1] [2] [2 err] [3] [4] [5] [6]


  • Ответ :
    Если необходимо определить наличие значения ошибки (которое может быть как результатом вычисления формулы, так и текстом) в указанной ячейке, то :

    Вариант I, II, III, IV, V
  • If IsError(Range("A1").Value) = True Then
       MsgBox "Ячейка содержит значение ошибки"
    Else
       MsgBox "Ячейка не содержит значение ошибки"
    End If
    If Application.IsError(Range("A1").Value) = True Then
       MsgBox "Ячейка содержит значение ошибки"
    Else
       MsgBox "Ячейка не содержит значение ошибки"
    End If
    If WorksheetFunction.IsError(Range("A1").Value) = True Then
       MsgBox "Ячейка содержит значение ошибки"
    Else
       MsgBox "Ячейка не содержит значение ошибки"
    End If
    If VarType(Range("A1").Value) = vbError Then
       MsgBox "Ячейка содержит значение ошибки"
    Else
       MsgBox "Ячейка не содержит значение ошибки"
    End If
    If TypeName(Range("A1").Value) = "Error" Then
       MsgBox "Ячейка содержит значение ошибки"
    Else
       MsgBox "Ячейка не содержит значение ошибки"
    End If
    If Evaluate("Type(A1)") = 16 Then '[Type(A1)] = 16 'Только для стиля ссылок A1
       MsgBox "Ячейка содержит значение ошибки"
    Else
       MsgBox "Ячейка не содержит значение ошибки"
    End If
    Важно : Не рекомендуется использовать стандартную функцию рабочего листа ТИП() (вариант V), если проверяемая ячейка, содержит текст, превышающий 255 символов, т.к. в этом случае - функция всегда будет возвращать TRUE(ИСТИНА)  По той же причине, в XL97 не рекомендуется использовать и стандартную функцию рабочего листа ЕОШИБКА() (вариант II)

    Если необходимо определить наличие только одной ошибки, а именно #Н/Д
    If Application.IsNA(Range("A1")) = True Then
       MsgBox "Ячейка содержит значение ошибки #Н/Д"
    Else
       MsgBox "Ячейка не содержит значение ошибки #Н/Д, но может содержать другие ошибки"
    End If
    Если необходимо определить наличие любой ошибки, кроме #Н/Д
    If Application.IsErr(Range("A1").Value) = True Then
       MsgBox "Ячейка содержит значение ошибки"
    Else
       MsgBox "Ячейка не содержит некоторых ошибок, но может содержать #Н/Д"
    End If
    Если кроме определения наличия ошибки, необходимо ещё и узнать тип этой ошибки, то :

    Вариант I.
    With ThisWorkbook.Worksheets(1).Range("A1")
         If IsError(.Value) = True Then
            Select Case .Value
                Case CVErr(xlErrNA)
                   MsgBox "#Н/Д"
                Case CVErr(xlErrRef)
                   MsgBox "#ССЫЛКА!"
                Case CVErr(xlErrDiv0)
                   MsgBox "#ДЕЛ/0!"
                Case CVErr(xlErrValue)
                   MsgBox "#ЗНАЧ!"
                Case CVErr(xlErrName)
                   MsgBox "#ИМЯ?"
                Case CVErr(xlErrNum)
                   MsgBox "#ЧИСЛО!"
                Case CVErr(xlErrNull)
                   MsgBox "#ПУСТО!"
            End Select
         Else
            MsgBox "Ячейка " & .Address(External:=True) & _
            " не содержит значений ошибки", vbInformation, ""
         End If
    End With
    With ThisWorkbook.Worksheets(1).Range("A1")
         If IsError(.Value) = True Then
            MsgBox Switch( _
            .Value = CVErr(xlErrNA), "#Н/Д", _
            .Value = CVErr(xlErrRef), "#ССЫЛКА!", _
            .Value = CVErr(xlErrDiv0), "#ДЕЛ/0!", _
            .Value = CVErr(xlErrValue), "#ЗНАЧ!", _
            .Value = CVErr(xlErrName), "#ИМЯ?", _
            .Value = CVErr(xlErrNum), "#ЧИСЛО!", _
            .Value = CVErr(xlErrNull), "#ПУСТО!")
         Else
            MsgBox "Ячейка " & .Address(External:=True) & _
            " не содержит значений ошибки", vbInformation, ""
         End If
    End With
    Комментарий : Учитывая особенность функции Switch имеет смысл использовать переменную типа Variant вместо свойства .Value

    Вариант II.
    With ThisWorkbook.Worksheets(1).Range("A1")
         If IsError(.Value) = True Then
            iNumber = Evaluate("ERROR.TYPE(" & .Address( _
            ReferenceStyle:=Application.ReferenceStyle, External:=True) & ")")
            iErrorType = Choose(iNumber, _
            "#ПУСТО!", "#ДЕЛ/0!", "#ЗНАЧ!", _
            "#ССЫЛКА!", "#ИМЯ?", "#ЧИСЛО!", "#Н/Д")
            MsgBox iErrorType, vbCritical, ""
         Else
            MsgBox "Ячейка " & .Address(External:=True) & _
            " не содержит значений ошибки", vbInformation, ""
         End If
    End With
    Вариант III.
    Можно использовать свойство Text, но этот вариант зависит от локализации, т.е. Rus/Eng/...

    Вариант IV. Актуально только для MS Excel XP
    В этой версии появилось новое свойство, которое позволяет определить содержит ли ячейка формулу, которая возвращает значение ошибки.
    If Range("A1").Errors(xlEvaluateToError).Value = True Then
       MsgBox "Вычисление формулы вызывает ошибку"
    Else
       MsgBox "Вычисление формулы не вызывает ошибку"
    End If

  • Ответ : Актуально только для MS Excel XP

    В MS Excel XP появилось новое свойство, которое позволяет определить содержит ли ячейка нижеприведённые ошибки :

    ОшибкаКонстантаЗначение
    Вычисление формулы вызывает ошибкуxlEvaluateToError1
    Текстовая дата с 2-х значным годомxlTextDate2
    Число сохранено как текстxlNumberAsText3
    Несогласующаяся формула в областиxlInconsistentFormula4
    Формула не охватывает смежные ячейкиxlOmittedCells5
    Не заблокирована ячейка, содержащая формулуxlUnlockedFormulaCells6
    Формула ссылается на пустые ячейкиxlEmptyCellReferences7


    Microsoft Excel 2003
    В этой версии, к вышеопубликованному списку ошибок, разработчики добавили ещё одну

    ОшибкаКонстантаЗначение
    Ошибки при проверке данных в спискахxlListDataValidation8


    Microsoft Excel 2007
    А в следующей, разработчики не только добавили ещё одну, но и изменили комментарии к правилам контроля ошибок (в руссифиц.версии)

    ОшибкаКонстантаЗначение
    Ячейки, которые содержат формулы, вызывающие ошибкиxlEvaluateToError1
    Ячейки, которые содержат годы, представленные 2 цифрамиxlTextDate2
    Числа, отформатированные как текст или с предшествующим апострофомxlNumberAsText3
    Формулы, не согласованные с остальными формулами в областиxlInconsistentFormula4
    Формулы, не охватывающие смежные ячейкиxlOmittedCells5
    Незаблокированные ячейки, содержащие формулыxlUnlockedFormulaCells6
    Формулы, которые ссылаются на пустые ячейкиxlEmptyCellReferences7
    В таблицу введены недопустимые данныеxlListDataValidation8
    Несогласованное вычисление формулы столбца в таблицахxlInconsistentListFormula9

    И если у Вас возникла необходимость перебрать ячейки определённого диапазона и изменить цвет заливки у ячеек, содержащих подобные ошибки, то Вы можете воспользоваться следующим макросом, разумеется, указав свой диапазон и цвет заливки.
  • Private Sub SetColorInCellError() 'Excel XP
        Dim iSource As Range, iCell As Range, iCount%
        Set iSource = [A1:C10]
        
        For Each iCell In iSource
            For iCount = 1 To 7
                If iCell.Errors(iCount).Value = True Then
                   iCell.Interior.Color = vbRed: Exit For
                End If
            Next
        Next
    End Sub
    Если же нужно изменить цвет заливки в зависимости от типа ошибки, то используйте следующий вариант, только не забудьте указать свой диапазон и цвет, т.е. Color или ColorIndex
    Private Sub SetColorInCellError2() 'Excel XP
        Dim iSource As Range, iCell As Range, iCount%
        Set iSource = [A1:C10]
        
        For Each iCell In iSource
            For iCount = 1 To 7
                If iCell.Errors(iCount).Value = True Then
                   iCell.Interior.ColorIndex = Choose(iCount, 53, 9, 3, 46, 45, 44, 40)
                   Exit For
                End If
            Next
        Next
    End Sub
    Private Sub SetColorInCellError2v2() 'Excel XP
        Dim iCount%, iArrColor As Variant
        Dim iSource As Range, iCell As Range
        
        Set iSource = ActiveSheet.Range("A1:C10")
        
        iArrColor = Array(13209, 128, 255, 26367, 39423, 52479, 10079487)
        
        For Each iCell In iSource
            For iCount = 1 To 7
                If iCell.Errors(iCount).Value = True Then
                   iCell.Interior.Color = iArrColor(iCount - 1)
                   Exit For
                End If
            Next
        Next
    End Sub
    Private Sub SetColorInCellError3()
        Dim iArrErr%(1 To 7, 1 To 2), iCount%
        Dim iSource As Range, iCell As Range
        
        Set iSource = ActiveWorkbook.Worksheets(1).Range("A1:F100")
        
        iArrErr(1, 1) = xlEvaluateToError:      iArrErr(1, 2) = 53
        'Вычисление формулы вызывает ошибку     Коричневый
        iArrErr(2, 1) = xlTextDate:             iArrErr(2, 2) = 9
        'Текстовая дата с 2-х значным годом     Темно-Красный
        iArrErr(3, 1) = xlNumberAsText:         iArrErr(3, 2) = 3
        'Число сохранено как текст              Красный
        iArrErr(4, 1) = xlInconsistentFormula:  iArrErr(4, 2) = 46
        'Несогласующаяся формула в области      Оранжевый
        iArrErr(5, 1) = xlOmittedCells:         iArrErr(5, 2) = 45
        'Формула не охватывает смежные ячейки   Светло-Оранжевый
        iArrErr(6, 1) = xlUnlockedFormulaCells: iArrErr(6, 2) = 44
        'Не заблокирована ячейка с формулой     Золотистый
        iArrErr(7, 1) = xlEmptyCellReferences:  iArrErr(7, 2) = 40
        'Формула ссылается на пустые ячейки     Светло-Коричневый
        
        Application.ScreenUpdating = False
        
        For Each iCell In iSource
            For iCount = 1 To 7 'UBound(iArrErr)
                If iCell.Errors(iArrErr(iCount, 1)).Value = True Then
                   iCell.Interior.ColorIndex = iArrErr(iCount, 2)
                   Exit For 'Обратите внимание на то, что одна ячейка
                   'может содержать более одной ошибки, и если Вы хотите
                   'получить весь список ошибок, то избавьтесь от Exit For
                End If
            Next
        Next
        
        Application.ScreenUpdating = True
    End Sub

  • Ответ :
    Если необходимо определить наличие значения ошибки (которое может быть как результатом вычисления формулы, так и текстом) в любой ячейке диапазона, то для этого можно использовать любой из нижеприведённых вариантов :
  • Function IsCellError(iDiapazon As Range) As Boolean
        IsCellError = IsError(Application.Sum(iDiapazon))
    End Function
    Примечание : Данная функция универсальна, т.к. не требовательна к ресурсам, а также позволяет определить наличие ошибок в целом столбце или диапазоне несмежных ячеек.
    Function IsCellError(iDiapazon As Range) As Boolean
        With Application
             IsCellError = .Or(.IsError(iDiapazon))
        End With
    End Function
    Важно : Не рекомендуется проверять диапазоны, содержащие большое количество ячеек, т.к. это может привести к возникновению ошибки (XL95, XL97) или "подвисанию" компьютера. Кроме того, если Вы попытаетесь определить наличие ошибок в целом столбце или диапазоне несмежных ячеек, то в этом случае функция всегда будет возвращать True
    Function IsCellError(iDiapazon As Range) As Boolean
        IsCellError = Evaluate("OR(ISERROR(" & _
        iDiapazon.Address(, , Application.ReferenceStyle, True) & "))")
    End Function
    Важно : Также не рекомендуется проверять диапазоны, содержащие большое количество ячеек, т.к. это может привести к "подвисанию" компьютера. Если же Вы попытаетесь "найти" ошибку в целом столбце, то в этом случае функция также возвратит True, впрочем, этого можно избежать, если указать диапазон на одну ячейку меньше, например, [A1:A65535] или [A2:A65536] Однако, при работе с диапазоном несмежных ячеек, в отличии от прошлой функции, возникнет ошибка.
    Function IsCellError(iDiapazon As Range) As Boolean
        With Application
             IsCellError = .Sum(.CountIf(iDiapazon, Array("#DIV/0!", _
             "#N/A", "#NAME?", "#NULL!", "#NUM!", "#REF!", "#VALUE!"))) > 0
        End With
    End Function
    Важно : Данную функцию нельзя * использовать при работе с диапазоном несмежных ячеек (* без использования дополнительного цикла)
    Function IsCellError(iDiapazon As Range) As Boolean
        For Each iError In Array("#DIV/0!", "#N/A", "#NAME?", _
            "#NULL!", "#NUM!", "#REF!", "#VALUE!")
            If Not iDiapazon.Find(iError, , xlValues, xlWhole) Is Nothing Then
               IsCellError = True
               Exit For
            End If
        Next
    End Function
    Примечание : Эта функция, равно как и предыдущая, будет также полезна и при поиске вполне определённых ошибок, например, если Вам понадобится найти ячейки, содержащие только #Н/Д, #ЗНАЧ! и #ДЕЛ/0!, то достаточно всего лишь использовать Array("#N/A", "#VALUE!", "#DIV/0!") Если Вы уверены, что в искомом диапазоне нет значений, начинающихся с #, то процесс поиска ошибок можно немного упростить, т.е.
    Function IsCellError(iDiapazon As Range) As Boolean
        IsCellError = Not iDiapazon.Find("#*", , _
                      xlValues, xlWhole) Is Nothing
    End Function
    Function IsCellError(iDiapazon As Range) As Boolean
        On Error Resume Next
    
        iCountFormulas& = iDiapazon.SpecialCells(xlFormulas, xlErrors).Count
        iCountConstants& = iDiapazon.SpecialCells(xlConstants, xlErrors).Count
        iCountCells& = iCountFormulas& + iCountConstants&
    
        IsCellError = CBool(iCountCells&)
    End Function
    Важно :
  • Количество несмежных ячеек/диапазонов, которые содержат значение ошибки, не должно превышать 8193
  • Использование данного метода приведёт к выполнению событий, типа Worksheet_SelectionChange, Workbook_SheetSelectionChange, XLApp_SheetSelectionChange
  • Рабочий лист - родитель не должен быть защищён
  • Function IsCellError(iDiapazon As Range) As Boolean
        Dim iCell As Range
        For Each iCell In iDiapazon
            If IsError(iCell) = True Then
               IsCellError = True
               Exit For
            End If
        Next
    End Function
    Примечание : Перебор всех ячеек диапазона не всегда лучшее решение поставленной задачи, особенно при работе с большим количеством ячеек и наличии альтернативных вариантов.
  • Ответ :
    Если необходимо найти все ячейки, содержащие определённое значение ошибки (которое может быть как результатом вычисления формулы, так и текстом), то для этого можно использовать обычный поиск (см. нижеприведённые примеры), не забывая, что в любой версии, в т.ч. и руссифицированной, в качестве "критерия" поиска, нужно использовать данные второго столбца.

    Что ищемЧто используем
    #Н/Д#N/A
    #ДЕЛ/0!#DIV/0!
    #ИМЯ?#NAME?
    #ЗНАЧ!#VALUE!
    #ССЫЛКА!#REF!
    #ПУСТО!#NULL!
    #ЧИСЛО!#NUM!

    Пример(ы) того, как можно получить диапазон содержащий все ячейки, в которых находится #Н/Д, при адаптации не забудьте указать свою рабочую книгу, рабочий лист, диапазон и искомое значение ошибки.
  • Private Sub RangeWithErrors()
        With ThisWorkbook.Worksheets(1).Range("A1:A100,B5,C10:C50")
             Dim iCell As Range, iDiapazon As Range
             Set iCell = .Find( _
             What:="#N/A", LookIn:=xlValues, LookAt:=xlWhole)
    
             If Not iCell Is Nothing Then
                iAddress$ = iCell.Address
                Set iDiapazon = iCell
                Do
                    Set iCell = .FindNext(After:=iCell)
                    Set iDiapazon = Union(iDiapazon, iCell)
                Loop While iCell.Address <> iAddress$
                'Здесь Вы можете работать с полученным диапазоном,
                'который всегда будет содержать, как минимум, одну ячейку
             End If
        End With
    End Sub
    Private Sub RangeWithErrors2()
        With ThisWorkbook.Worksheets(1).UsedRange
             Dim iCell As Range, iDiapazon As Range
             Set iCell = .Find("#N/A", , xlValues, xlWhole)
    
             If Not iCell Is Nothing Then
                Set iDiapazon = iCell
                For iCount& = 1 To Application.CountIf(.Cells, "#N/A")
                    Set iCell = .FindNext(iCell)
                    Set iDiapazon = Union(iCell, iDiapazon)
                Next
                'Здесь Вы можете работать с полученным диапазоном,
                'который всегда будет содержать, как минимум, одну ячейку
             End If
        End With
    End Sub

  • Ответ :
    Для того, чтобы очистить содержимое всех ячеек, содержащих определённое значение ошибки (которое может быть как результатом вычисления формулы, так и текстом), можно использовать предыдущий [FAQ561] и просто добавить метод .ClearContents об'екта Range или же использовать "сокращённый" вариант.

    Код, позволяющий в незащищённом активном рабочем листе, очистить ячейки, в которых находится #Н/Д прилагается. Не забывайте, что лист и диапазон взяты только для примера, поэтому, Вы можете указать свою рабочую книгу, рабочий лист, диапазон, а также искомое(удаляемое) значение ошибки.
  • Private Sub ClearCellErr()
        Dim iSource As Range, iCell As Range
        Set iSource = ActiveSheet.UsedRange
        Set iCell = iSource.Find("#N/A", , xlValues, xlWhole)
        
        Do Until iCell Is Nothing
           iCell.ClearContents
           Set iCell = iSource.FindNext
        Loop
    End Sub
    Private Sub ClearCellErr2()
        Dim iSource As Range, iCount&
        Set iSource = ActiveSheet.UsedRange
    
        For iCount = 1 To Application.CountIf(iSource, "#N/A")
            iSource.Find("#N/A", , xlValues, xlWhole).ClearContents
        Next
    End Sub

    Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

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