Длинный вложенный оператор IF создает ошибку времени выполнения 1004 в Excel VBA

У меня есть формула, которая отлично работает в электронной таблице, но когда я пытаюсь автоматизировать создание формулы с помощью VBA, я получаю ошибку времени выполнения 1004.

У меня есть трехэтапный процесс, каждый с датой начала и окончания (так что 6 отдельных столбцов даты). Эта формула смотрит на даты ряда и вычисляет время, потерянное между каждым этапом, с помощью функции ЧИСТРАБДНИ.

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

Мне интересно, проблема ли в длине формулы? Я попытался заменить двойные кавычки на & Chr(34) & Chr(34) & и попытался присвоить формулу переменной. Я могу заставить его работать, если я добавлю его как текст, используя .value вместо .formulaR1C1, или если я добавлю апостроф в начало строки формулы. Похоже, что проблема заключается в формуле. Любая помощь приветствуется.

Формула из ячейки в электронной таблице:

=IF(AND(I2="",J2="",K2="",L2=""),"",IF(AND(G2="",H2="",K2="",L2=""),"",IF(AND(G2="",H2="",I2="",J2=""),"",IF(AND(H2<>"",I2<>"",J2<>"",K2<>""),IF(NETWORKDAYS(H2,I2)-2+NETWORKDAYS(J2,K2)-2<1,"",NETWORKDAYS(H2,I2)-2+NETWORKDAYS(J2,K2)-2),IF(AND(H2<>"",I2<>""),IF(NETWORKDAYS(H2,I2)-2<1,"",NETWORKDAYS(H2,I2)-2),IF(AND(H2<>"",K2<>""),IF(NETWORKDAYS(H2,K2)-2<1,"",NETWORKDAYS(H2,K2)-2),IF(AND(J2<>"",K2<>""),IF(NETWORKDAYS(J2,K2)-2<1,"",NETWORKDAYS(J2,K2)-2),"")))))))

Код, который записывает макрос, когда я ввожу формулу в ячейку:

ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-10]="""",RC[-9]="""",RC[-8]="""",RC[-7]=""""),"""",IF(AND(RC[-12]="""",RC[-11]="""",RC[-8]="""",RC[-7]=""""),"""",IF(AND(RC[-12]="""",RC[-11]="""",RC[-10]="""",RC[-9]=""""),"""",IF(AND(RC[-11]<>"""",RC[-10]<>"""",RC[-9]<>"""",RC[-8]<>""""),IF(NETWORKDAYS(RC[-11],RC[-10])-2+NETWORKDAYS(RC[-9],RC[-8])-2<1,"""",NETWORKDAYS(RC[-11],RC[-10])-2+NETWORKDAYS(RC[" & _
        "8])-2),IF(AND(RC[-11]<>"""",RC[-10]<>""""),IF(NETWORKDAYS(RC[-11],RC[-10])-2<1,"""",NETWORKDAYS(RC[-11],RC[-10])-2),IF(AND(RC[-11]<>"""",RC[-8]<>""""),IF(NETWORKDAYS(RC[-11],RC[-8])-2<1,"""",NETWORKDAYS(RC[-11],RC[-8])-2),IF(AND(RC[-9]<>"""",RC[-8]<>""""),IF(NETWORKDAYS(RC[-9],RC[-8])-2<1,"""",NETWORKDAYS(RC[-9],RC[-8])-2),"""")))))))"

excel vba nested-if
person Omega73    schedule 31.03.2015    source источник
comment
Пожалуйста, исправьте форматирование кода ...   -  person David Zemens    schedule 31.03.2015
comment
Также укажите, в какую ячейку вы пытаетесь вставить эту формулу.   -  person David Zemens    schedule 31.03.2015
comment
Извините, я не знал, как отсортировать форматирование (это мой первый пост), но спасибо тому, кто это исправил.   -  person Omega73    schedule 01.04.2015
comment
Не имеет значения, в какую ячейку входит формула, проблема заключается в процессе выполнения этого с помощью VBA. Я собираюсь поместить формулу в столбец с тысячами строк данных.   -  person Omega73    schedule 01.04.2015
comment
О, но это имеет значение из-за смещений, формула может относиться к недопустимым диапазонам и т. Д. Fwiw, я ввожу формулу, и она ошибается в любой ячейке, поэтому проблема, похоже, связана с самой строкой formyla. В любом случае, похоже, у вас уже есть решение, ура.   -  person David Zemens    schedule 01.04.2015

Ответы (1)


arrow_upward
0
arrow_downward

Рассмотреть возможность:

Sub EvilFormula()
    Dim st As String, DQ As String
    DQ = Chr(34) & Chr(34)
    st = "=IF(AND(I2=~,J2=~,K2=~,L2=~),~,IF(AND(G2=~,H2=~,K2=~,L2=~),~,IF(AND(G2=~,H2=~,I2=~,J2=~),~,IF(AND(H2<>~,I2<>~,J2<>~,K2<>~),IF(NETWORKDAYS(H2,I2)-2+NETWORKDAYS(J2,K2)-2<1,~,NETWORKDAYS(H2,I2)-2+NETWORKDAYS(J2,K2)-2),IF(AND(H2<>~,I2<>~),IF(NETWORKDAYS(H2,I2)-2<1,~,NETWORKDAYS(H2,I2)-2),IF(AND(H2<>~,K2<>~),IF(NETWORKDAYS(H2,K2)-2<1,~,NETWORKDAYS(H2,K2)-2),IF(AND(J2<>~,K2<>~),IF(NETWORKDAYS(J2,K2)-2<1,~,NETWORKDAYS(J2,K2)-2),~)))))))"
    st = Replace(st, "~", DQ)
    ActiveCell.Formula = st
End Sub

Здесь мы сначала создаем String, который представляет формулу, но использует тильды вместо пар двойных кавычек. Мы заменяем тильды и вносим формулу.

Я использую это, чтобы избежать мигрени.

person Gary's Student    schedule 31.03.2015
comment
Спасибо за эту маленькую жемчужину, она отлично работает !!! Также обнадеживает то, что я выкладывал правильное дерево, когда пытался назначить его строке и заменить двойные кавычки. Это, безусловно, эффективное средство от мигрени :) Большое спасибо, что нашли время взглянуть на это. - person Omega73; 01.04.2015