#REF! вставлен как значение, но не рассматривается как таковое при циклировании

Я только что выпустил надстройку Excel в своем отделе сегодня, над которой я работал в течение последних 2 месяцев, который проверяет около 30 ошибок проверки. У меня есть ловушка ошибок, обрабатываемая во всех ситуациях (как это видно прямо сейчас), но сегодня я получил ужасный звонок для пробуждения, когда получил автоматические электронные письма (функцию, встроенную в обработку ошибок) для двух жизненно важных ошибок. Я уже разместил вопрос о первой ошибке здесь и решил, что я начну новый вопрос для второй ошибки, поскольку речь идет о чем-то, не связанном с первым.

Мой код выглядит следующим образом

Private Sub symbolCheck() On Error GoTo ErrHandler Application.StatusBar = "(3/16) Checking for invalid symbols" Dim MyArray As Variant Dim replacementsMade As Boolean replacementsMade = False MyArray = ActiveSheet.UsedRange For i = LBound(MyArray) To UBound(MyArray) For j = LBound(MyArray, 2) To UBound(MyArray, 2) If MyArray(i, j) <> "" Then 'Apostrophe/Closing Single Quote If InStr(1, MyArray(i, j), "'") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "'", Chr(39)) If replacementsMade = False Then replacementsMade = True End If End If 'Apostrophe If InStr(1, MyArray(i, j), "`") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "`", Chr(39)) If replacementsMade = False Then replacementsMade = True End If End If 'Opening Single Quote If InStr(1, MyArray(i, j), "'") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "'", Chr(39)) If replacementsMade = False Then replacementsMade = True End If End If 'Double Open Quotes If InStr(1, MyArray(i, j), "“") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "“", """") If replacementsMade = False Then replacementsMade = True End If End If 'Double Closing Quotes If InStr(1, MyArray(i, j), "”") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "”", """") If replacementsMade = False Then replacementsMade = True End If End If 'Dash If InStr(1, MyArray(i, j), "–") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "–", "-") If replacementsMade = False Then replacementsMade = True End If End If 'Registered Trademark (R) If InStr(1, MyArray(i, j), "®") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "®", "(R)") If replacementsMade = False Then replacementsMade = True End If End If 'Trademark (TM) If InStr(1, MyArray(i, j), "™") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "™", "(TM)") If replacementsMade = False Then replacementsMade = True End If End If 'Degree Symbol If InStr(1, MyArray(i, j), "°") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "°", " degrees") If replacementsMade = False Then replacementsMade = True End If End If 'Multiplication/x Symbol If InStr(1, MyArray(i, j), "×") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "×", "x") If replacementsMade = False Then replacementsMade = True End If End If 'Upside-Down Question Mark Symbol If InStr(1, MyArray(i, j), "¿") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "¿", "") If replacementsMade = False Then replacementsMade = True End If End If 'Solid Bullet Symbol If InStr(1, MyArray(i, j), "•") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "•", "") If replacementsMade = False Then replacementsMade = True End If End If 'Triple Dots Symbol If InStr(1, MyArray(i, j), "…") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "…", "...") If replacementsMade = False Then replacementsMade = True End If End If 'Euro Symbol If InStr(1, MyArray(i, j), "€") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "€", "") If replacementsMade = False Then replacementsMade = True End If End If 'Linebreak Symbol If InStr(1, MyArray(i, j), "|") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "|", ",") If replacementsMade = False Then replacementsMade = True End If End If ' 'Less Than Symbol ' If InStr(1, MyArray(i, j), "<") > 0 Then ' MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "<", "<") ' End If ' 'Greater Than Symbol ' If InStr(1, MyArray(i, j), ">") > 0 Then ' MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), ">", ">") ' End If 'Half Fraction If InStr(1, MyArray(i, j), "½") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "½", " 1/2") If replacementsMade = False Then replacementsMade = True End If End If 'Three Quarter Fraction If InStr(1, MyArray(i, j), "¾") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "¾", " 3/4") If replacementsMade = False Then replacementsMade = True End If End If 'One Quarter Fraction If InStr(1, MyArray(i, j), "¼") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "¼", " 1/4") If replacementsMade = False Then replacementsMade = True End If End If End If Next j Next i If replacementsMade Then ActiveSheet.UsedRange = MyArray End If Set MyArray = Nothing Exit Sub ErrHandler: Err.Raise Err.Number, "symbolCheck", Err.Description End Sub 

Эта ошибка возникает на линии

 If MyArray(i, j) <> "" Then 

Когда i = 209 и j = 60, я сделал несколько попыток и посмотрел в массив, чтобы узнать, какое значение было в этом месте. Значение просто говорит об Error 2023 когда я посмотрел на значения Watchlist для слота массива. Итак, я посмотрел на ячейку, которая соответствовала этим значениям i и j, и, увы, я наконец понял, почему возникла ошибка. Значение в ячейке было первоначально формулой с опорными ошибками, и поскольку я копировал / вставлял как значения перед запуском этого суб, я думал, что все будет в порядке. Я понятия не имел, что #REF! не было видно как открытый текст?

Что приводит меня к моему вопросу

Как я могу справиться с этой ситуацией? Точнее, как я смогу избавиться от #REF! значения в таблице (без использования Find / Replace), если #REF! не рассматривается как открытый текст даже после копирования / вставки в качестве значения?

Решение Clear #REF! Значения в таблице

Вы можете использовать SpecialCells для устранения ошибок.

 ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents 'Or change .Value to another value, delete cells, etc. as desired 

Решение для обработки #REF! Ошибки в массиве

Вы можете использовать функцию VBA ISERROR () для захвата каждого #REF! и затем обрабатывать по желанию.

Измените свой код следующим образом:

 Private Sub symbolCheck() On Error GoTo ErrHandler Application.StatusBar = "(3/16) Checking for invalid symbols" Dim MyArray As Variant Dim replacementsMade As Boolean replacementsMade = False MyArray = ActiveSheet.UsedRange For i = LBound(MyArray) To UBound(MyArray) For j = LBound(MyArray, 2) To UBound(MyArray, 2) If IsError(MyArray(i, j)) Then 'Handle the #REF! here ElseIf MyArray(i, j) <> "" Then 'Apostrophe/Closing Single Quote If InStr(1, MyArray(i, j), "'") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "'", Chr(39)) If replacementsMade = False Then replacementsMade = True End If End If 'Apostrophe If InStr(1, MyArray(i, j), "`") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "`", Chr(39)) If replacementsMade = False Then replacementsMade = True End If End If 'Opening Single Quote If InStr(1, MyArray(i, j), "'") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "'", Chr(39)) If replacementsMade = False Then replacementsMade = True End If End If 'Double Open Quotes If InStr(1, MyArray(i, j), "“") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "“", """") If replacementsMade = False Then replacementsMade = True End If End If 'Double Closing Quotes If InStr(1, MyArray(i, j), "”") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "”", """") If replacementsMade = False Then replacementsMade = True End If End If 'Dash If InStr(1, MyArray(i, j), "–") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "–", "-") If replacementsMade = False Then replacementsMade = True End If End If 'Registered Trademark (R) If InStr(1, MyArray(i, j), "®") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "®", "(R)") If replacementsMade = False Then replacementsMade = True End If End If 'Trademark (TM) If InStr(1, MyArray(i, j), "™") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "™", "(TM)") If replacementsMade = False Then replacementsMade = True End If End If 'Degree Symbol If InStr(1, MyArray(i, j), "°") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "°", " degrees") If replacementsMade = False Then replacementsMade = True End If End If 'Multiplication/x Symbol If InStr(1, MyArray(i, j), "×") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "×", "x") If replacementsMade = False Then replacementsMade = True End If End If 'Upside-Down Question Mark Symbol If InStr(1, MyArray(i, j), "¿") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "¿", "") If replacementsMade = False Then replacementsMade = True End If End If 'Solid Bullet Symbol If InStr(1, MyArray(i, j), "•") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "•", "") If replacementsMade = False Then replacementsMade = True End If End If 'Triple Dots Symbol If InStr(1, MyArray(i, j), "…") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "…", "...") If replacementsMade = False Then replacementsMade = True End If End If 'Euro Symbol If InStr(1, MyArray(i, j), "€") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "€", "") If replacementsMade = False Then replacementsMade = True End If End If 'Linebreak Symbol If InStr(1, MyArray(i, j), "|") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "|", ",") If replacementsMade = False Then replacementsMade = True End If End If ' 'Less Than Symbol ' If InStr(1, MyArray(i, j), "<") > 0 Then ' MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "<", "<") ' End If ' 'Greater Than Symbol ' If InStr(1, MyArray(i, j), ">") > 0 Then ' MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), ">", ">") ' End If 'Half Fraction If InStr(1, MyArray(i, j), "½") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "½", " 1/2") If replacementsMade = False Then replacementsMade = True End If End If 'Three Quarter Fraction If InStr(1, MyArray(i, j), "¾") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "¾", " 3/4") If replacementsMade = False Then replacementsMade = True End If End If 'One Quarter Fraction If InStr(1, MyArray(i, j), "¼") > 0 Then MyArray(i, j) = WorksheetFunction.Substitute(MyArray(i, j), "¼", " 1/4") If replacementsMade = False Then replacementsMade = True End If End If End If Next j Next i If replacementsMade Then ActiveSheet.UsedRange = MyArray End If Set MyArray = Nothing Exit Sub ErrHandler: Err.Raise Err.Number, "symbolCheck", Err.Description End Sub 
  • Удаление строк на основе значения FALSE в Excel VBA (ошибка выполнения 91)
  • Excel VBA Vlookup Runtime Ошибка 1004
  • Экспорт XML-файла из Excel
  • Ошибка выполнения 438 при копировании из одной книги в другую
  • «Ошибка выполнения 380: недопустимое значение свойства»
  • Как добавить имя файла при импорте нескольких файлов Excel в одну таблицу Access
  • Excel 2013 сравнивает даты в разных строках с использованием макроса «Ошибка времени выполнения 13, несоответствие типа»
  • Ошибка времени выполнения 9 в vba
  • Ошибка выполнения 13 в цикле for i, которая использовалась для работы
  • Ошибка времени выполнения 5 - Неверная процедура Вызов или аргумент
  • Настройка Excel FormatConditions Цвет шрифта во время выполнения
  • Interesting Posts
    Давайте будем гением компьютера.