Блог

Главная - Вопросы по программированию - Формула массива длиной более 255 символов и .Заменить нерабочий VBA

Формула массива длиной более 255 символов и .Заменить нерабочий VBA

#arrays #excel #vba #array-formulas

#массивы #excel #vba #массив-формулы

Вопрос:

У меня возникла проблема с индексом / совпадением формулы Excel с IFERROR, и он отлично работает в Excel, когда я запускаю его вручную, однако после записи в макрос он не работает. эта формула создана для выполнения сопоставления и поиска при нескольких условиях и получения выходного результата.

При запуске макроса я получаю следующую ошибку — ошибка времени выполнения ‘1004’: невозможно установить свойство FormulaArray класса Range.

    Range("O2").Select

   Selection.FormulaArray = _
 "=IFERROR(INDEX('[NAL for Macro.xlsb]Sheet1'!C13,MATCH(1,('[NAL for Macro.xlsb]Sheet1'!C8=RC[-8])*(LEFT('[NAL for Macro.xlsb]Sheet1'!C3,15)=LEFT(RC[-13],15)),0)),IFERROR(VLOOKUP(RC[-8],'[NAL for Macro.xlsb]Sheet1'!C8:C15,6,0),IFERROR(VLOOKUP(RC[-8],'[NAL for Macro.xlsb]Sheet1'!C9:C15,5,0),(VLOOKUP(LEFT(RC[-13],15)amp;""*"",'[NAL for Macro.xlsb]Sheet1'!C3:C15,11,0)))))"

затем я разделил формулу на две части, как показано ниже. Выполняется кодирование макросов и не выдает никаких ошибок, но замена не происходит. Я немного новичок в макросах и старался изо всех сил, насколько мне известно, но это не работает, может кто-нибудь, пожалуйста, помочь мне.

  Sub Macro19()
 '
 ' Macro19 Macro
 '

 '    
  With ActiveSheet.Range("O2")
 Selection.FormulaArray = "=IFERROR(INDEX('[NAL for Macro.xlsb]Sheet1'!C13,MATCH(1,('[NAL for Macro.xlsb]Sheet1'!C8=RC[-8])*(LEFT('[NAL for Macro.xlsb]Sheet1'!C3,15)=LEFT(RC[-13],15)),0)),XX)"

  .Replace "XX", "IFERROR(VLOOKUP(RC[-8],'[NAL for Macro.xlsb]Sheet1'!C8:C15,6,0),IFERROR(VLOOKUP(RC[-8],'[NAL for Macro.xlsb]Sheet1'!C9:C15,5,0),(VLOOKUP(LEFT(RC[-13],15)amp;""*"",'[NAL for Macro.xlsb]Sheet1'!C3:C15,11,0)))))"

      End With
      End Sub

До сих пор я менял кодировку на .Replace «‘ws'», «‘[NAL для Macro.xlsb]Sheet1′» и ниже приведен пересмотренный вариант. Он выполняется успешно, и ошибка не возвращается, но .Замена пока не происходит, и результат вывода приходит # NA. Пожалуйста, помогите решить эту проблему.

  Sub Macro20()
 '
 ' Macro20 Macro
 '

 '

   With ActiveSheet.Range("O2")
         .FormulaArray = _
        "=IFERROR(INDEX('ws'!C13,MATCH(1,('ws'!C8=RC[-8])*(LEFT('ws'!C3,15)=LEFT(RC[-13],15)),0)),IFERROR(VLOOKUP(RC[-8],'ws'!C8:C15,6,0),IFERROR(VLOOKUP(RC[-8],'ws'!C9:C15,5,0),(VLOOKUP(LEFT(RC[-13],15)amp;""*"",'ws'!C3:C15,11,0)))))"

       .Replace "'ws'", "'[NAL for Macro.xlsb]Sheet1'"
     End With

    End Sub

Комментарии:

1. Здесь есть хороший совет по этому вопросу: mrexcel.com/board/threads/long-array-formulas-in-vba.687125 . Возможно, в частичных формулах есть какая-то синтаксическая ошибка. Вы пробовали использовать числовое значение вместо XX?

2. Я выполнил формулу частично, и она выполняется успешно, и когда я пытаюсь выполнить полную формулу за один раз; получаю ошибку. Да, я также пробовал числовые значения, но получал ту же ошибку.

3. Я бы искал несколько случаев, которые я могу заменить, затем работал бы в обратном направлении от замены формулы и отмечал порядок (но не редактировал формулу вручную, чтобы попытаться избежать ошибки) для замены при построении предложения. Моей первой заменой было бы .Replace "'ws'", "'[NAL for Macro.xlsb]Sheet1'" (апострофы создают хорошие совпадения строк), что значительно сокращает формулу, но оказывает минимальное влияние. Заменяйте дополнительные вспомогательные функции до тех пор, пока они не будут в пределах символов

4. На самом деле, эта единственная замена должна работать в соответствии с charactercounttool.com тот самый . Текст FormulaArray тогда будет составлять 222 символа

5. Я сделал то же самое, но . Replace не работает, означает, что он не заменяет «‘ws'» на «‘[NAL для Macro.xlsb]Sheet1′». Код выполняется без ошибок, а результат вывода отображается как #NA .

Метки: