Проверка формул УФ ячеек на наличие ошибок #ССЫЛКА! и #REF!

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Проверка формул УФ ячеек на наличие ошибок #ССЫЛКА! и #REF! (Слепил процедуру. Помогите "допилить")

Alex_ST

Дата: Среда, 28.11.2012, 09:56 | Сообщение № 1

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3213

Репутация: 609 ±

Замечаний: 0% ±

2003

В процессе работы с таблицами ячейки/строки/столбцы могут удаляться/перемещаться через"вырезать-вставить".
Формулы листа сразу покажут ошибки при их возникновении. А вот формулы УФ на листе просто перестанут работать и в результате - всего лишь отсутствие должного форматирования, на которое рассчитывал cry

Слепил с помощью знатоков на Планете процедуру, которая просмотрит все ячейки с УФ на листе и поверит их формулы на наличие "волшебных" слов #ССЫЛКА! или #REF! (специально сделал для любителей английской локали biggrin

)

[vba]

Code

Sub Check_CondForm()  ' проверить формулы условного форматирования ячеек на наличие ошибок типа #ССЫЛКА!
'http://www.planetaexcel.ru/forum.php?thread_id=47802 , http://www.excelworld.ru/forum/2-2901-1#31544
        Dim rCell As Range, rFCCells As Range, nFC&
        Set rFCCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)
        If rFCCells Is Nothing Then Exit Sub
        For Each rCell In rFCCells
           For nFC = 1 To rCell.FormatConditions.Count
              With rCell.FormatConditions(nFC)
                 If .Type = xlExpression Then
                    If .Formula1 Like "*[#]ССЫЛКА!*" Or .Formula1 Like "*[#]REF!*" Then
                       rCell.Activate
                       Select Case MsgBox("Обнаружена ошибка аргумента формулы условного форматирования ячейки " & rCell.Address(0, 0) & vbCrLf & _
                        "Условие " & nFC & " : " & .Formula1 & vbCrLf & vbCrLf & _
                        "Выберите действие:" & vbCrLf & _
                        """ДА"" - Исправить, ""НЕТ"" - Искать дальше, ""ОТМЕНА"" - Выйти", _
                        vbYesNoCancel + vbInformation, "Ошибка формулы УФ!")
                          Case vbYes: Application.Dialogs(xlDialogConditionalFormatting).Show ' xlDialogConditionalFormatting не имеет аргументов, поэтому приходится активизировать ячейку
                          Case vbCancel: Exit Sub
                       End Select
                    End If
                 End If
              End With
           Next nFC
        Next rCell
End Sub

[/vba]

Вроде, всё работает.
В прилагаемом примере при удалении столбцов, содержащих ячейки-аргументы формул УФ, макрос ячейки с "искалеченными" формулами находит уверенно.
Вот только меня мучают сомнения, а не нужно ли проверять ещё и Formula2 ?
Наверное, не помешало бы, но ведь параметр Formula2 есть не у каждой формулы УФ и прямое обращение к нему в большинстве случаев вызовет ошибку.
Что-то я не соображу, как ПРОСТО добавить проверку Formula2 если она существует?

Тем, кто успел скачать до 10:10 - Я ПЕРЕЗАЛИЛ ПРИМЕР! (прошу прощения за ошибку)

)

[vba]

Code

[/vba]

К сообщению приложен файл: Check_CondForm.xls (43.0 Kb)

С уважением,
Алексей
MS Excel 2003 - the best!!!

Сообщение отредактировал Alex_ST - Среда, 28.11.2012, 10:13

Ответить

СообщениеВ процессе работы с таблицами ячейки/строки/столбцы могут удаляться/перемещаться через"вырезать-вставить".
Формулы листа сразу покажут ошибки при их возникновении. А вот формулы УФ на листе просто перестанут работать и в результате - всего лишь отсутствие должного форматирования, на которое рассчитывал cry

)

[vba]

Code

[/vba]

Автор - Alex_ST
Дата добавления - 28.11.2012 в 09:56

Alex_ST

Дата: Пятница, 30.11.2012, 16:08 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3213

Репутация: 609 ±

Замечаний: 0% ±

2003

Сам допилил на досуге:

[vba]

Code

Sub Check_CondForm()  ' проверить формулы условного форматирования ячеек на наличие ошибок типа #ССЫЛКА! и #REF!
'http://www.planetaexcel.ru/forum.php?thread_id=47802 ,  http://www.excelworld.ru/forum/2-2901-1#31544
     Dim rCell As Range, rFCCells As Range, nFC&, sFormula$
     Set rFCCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)
     If rFCCells Is Nothing Then Exit Sub
     For Each rCell In rFCCells
        For nFC = 1 To rCell.FormatConditions.Count
           With rCell.FormatConditions(nFC)
              sFormula = .Formula1
              If .Type = xlExpression Then   ' xlExpression==2
                 sFormula = "Формула " & .Formula1
              ElseIf .Type = xlCellValue Then    ' xlCellValue==1
                 ' xlBetween == 1 xlNotBetween == 2 xlEqual == 3 xlNotEqual == 4 xlGreater == 5 xlLess == 6 xlGreaterEqual == 7 xlLessEqual == 8
                 sFormula = "Значение " & Choose(.Operator, "между", "вне", "равно", "не равно", "больше", "меньше", "больше или равно", "меньше или равно") & " " & .Formula1
                 If .Operator < 3 Then sFormula = sFormula & " и " & .Formula2
              End If
              If sFormula Like "*[#]ССЫЛКА!*" Or sFormula Like "*[#]REF!*" Then
                 rCell.Activate
                 Select Case MsgBox("Ошибка аргументов формулы условного форматирования ячейки " & rCell.Address(0, 0) & vbCrLf & _
                     "Условие " & nFC & " : " & sFormula & vbCrLf & vbCrLf & _
                     "Выберите действие:" & vbCrLf & _
                     """ДА"" - Исправить, ""НЕТ"" - Искать дальше, ""ОТМЕНА"" - Выйти", _
                     vbYesNoCancel + vbInformation, "Ошибка формулы УФ!")
                    Case vbYes: Application.Dialogs(xlDialogConditionalFormatting).Show      ' xlDialogConditionalFormatting не имеет аргументов, поэтому приходится активизировать ячейку
                    Case vbCancel: Exit Sub
                 End Select
              End If
           End With
        Next nFC
     Next rCell
End Sub

[/vba]

Сам допилил на досуге:

[vba]

Code