Добрый день, подскажите пожалуйста. Встал вопрос о вставке макросом формул исходя из количества аргументов... Написал вот такой код..При этом первые три формулы встают, а четвертая по (Х), что длинная ругается. Может кто видит ошибку? [vba]
Sheets("пересчет").Range("X2:X" & Cells(Rows.Count, 6).End(xlUp).Row).FormulaR1C1 = "=IF(RC[+44]=""менеджерская "",IF(AND(ISNUMBER(IF(ISNUMBER(VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[44]),матрица!R8C3:R8C9,0),FALSE)),VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE),""нет нижней КВ"")),ISNUMBER((IF(ISNUMBER(VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:" & _ "ATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)),VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[44]),матрица!R8C3:R8C9,0),FALSE),""нет нижней КВ"")))),VLOOKUP(ROUNDDOWN(RC[-3],0),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)+(RC[-3]-(ROUNDDOWN(RC[-3],0)))*(VLOOKUP((ROUNDUP(RC[-3],0)),ма" & _ "C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)-VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)),""нет данных для расчета""),IF(RC[+44]=""агентская "",IF(AND(ISNUMBER(IF(ISNUMBER(VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C" & _ "E)),VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE),""нет нижней КВ"")),ISNUMBER((IF(ISNUMBER(VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)),VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R" & _ "LSE),""нет нижней КВ"")))),VLOOKUP(ROUNDDOWN(RC[-3],0),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)+(RC[-3]-(ROUNDDOWN(RC[-3],0)))*(VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)-VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),ма" & _ "3:R8C9,0),FALSE)),""нет данных для расчета""),0))"
End Sub
[/vba] сама формула выглядет так:
Код
=ЕСЛИ(RC[44]="менеджерская ";ЕСЛИ(И(ЕЧИСЛО(ЕСЛИ(ЕЧИСЛО(ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ);"нет нижней КВ"));ЕЧИСЛО((ЕСЛИ(ЕЧИСЛО(ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ);"нет нижней КВ"))));ВПР(ОКРУГЛВНИЗ(RC[-3];0);матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ)+(RC[-3]-(ОКРУГЛВНИЗ(RC[-3];0)))*(ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ)-ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));"нет данных для расчета");ЕСЛИ(RC[44]="агентская ";ЕСЛИ(И(ЕЧИСЛО(ЕСЛИ(ЕЧИСЛО(ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ);"нет нижней КВ"));ЕЧИСЛО((ЕСЛИ(ЕЧИСЛО(ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ);"нет нижней КВ"))));ВПР(ОКРУГЛВНИЗ(RC[-3];0);матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ)+(RC[-3]-(ОКРУГЛВНИЗ(RC[-3];0)))*(ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ)-ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));"нет данных для расчета");0))
Добрый день, подскажите пожалуйста. Встал вопрос о вставке макросом формул исходя из количества аргументов... Написал вот такой код..При этом первые три формулы встают, а четвертая по (Х), что длинная ругается. Может кто видит ошибку? [vba]
Sheets("пересчет").Range("X2:X" & Cells(Rows.Count, 6).End(xlUp).Row).FormulaR1C1 = "=IF(RC[+44]=""менеджерская "",IF(AND(ISNUMBER(IF(ISNUMBER(VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[44]),матрица!R8C3:R8C9,0),FALSE)),VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE),""нет нижней КВ"")),ISNUMBER((IF(ISNUMBER(VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:" & _ "ATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)),VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[44]),матрица!R8C3:R8C9,0),FALSE),""нет нижней КВ"")))),VLOOKUP(ROUNDDOWN(RC[-3],0),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)+(RC[-3]-(ROUNDDOWN(RC[-3],0)))*(VLOOKUP((ROUNDUP(RC[-3],0)),ма" & _ "C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)-VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)),""нет данных для расчета""),IF(RC[+44]=""агентская "",IF(AND(ISNUMBER(IF(ISNUMBER(VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C" & _ "E)),VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE),""нет нижней КВ"")),ISNUMBER((IF(ISNUMBER(VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)),VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R" & _ "LSE),""нет нижней КВ"")))),VLOOKUP(ROUNDDOWN(RC[-3],0),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)+(RC[-3]-(ROUNDDOWN(RC[-3],0)))*(VLOOKUP((ROUNDUP(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),матрица!R8C3:R8C9,0),FALSE)-VLOOKUP((ROUNDDOWN(RC[-3],0)),матрица!R10C3:R109C9,MATCH(CONCATENATE(RC[-20],RC[+44]),ма" & _ "3:R8C9,0),FALSE)),""нет данных для расчета""),0))"
End Sub
[/vba] сама формула выглядет так:
Код
=ЕСЛИ(RC[44]="менеджерская ";ЕСЛИ(И(ЕЧИСЛО(ЕСЛИ(ЕЧИСЛО(ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ);"нет нижней КВ"));ЕЧИСЛО((ЕСЛИ(ЕЧИСЛО(ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ);"нет нижней КВ"))));ВПР(ОКРУГЛВНИЗ(RC[-3];0);матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ)+(RC[-3]-(ОКРУГЛВНИЗ(RC[-3];0)))*(ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ)-ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));"нет данных для расчета");ЕСЛИ(RC[44]="агентская ";ЕСЛИ(И(ЕЧИСЛО(ЕСЛИ(ЕЧИСЛО(ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ);"нет нижней КВ"));ЕЧИСЛО((ЕСЛИ(ЕЧИСЛО(ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ);"нет нижней КВ"))));ВПР(ОКРУГЛВНИЗ(RC[-3];0);матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ)+(RC[-3]-(ОКРУГЛВНИЗ(RC[-3];0)))*(ВПР((ОКРУГЛВВЕРХ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ)-ВПР((ОКРУГЛВНИЗ(RC[-3];0));матрица!R10C3:R109C9;ПОИСКПОЗ(СЦЕПИТЬ(RC[-20];RC[44]);матрица!R8C3:R8C9;0);ЛОЖЬ));"нет данных для расчета");0))
Спасибо, я в экселе и тем более в VBA профан...Я сначала записал формулу в ячейку через рекодер, а потом просто скопировал получившийся вид... Вы хотите сказать, что при переносе в коду формула была деформирована и ее нужно восстановить? Я признаться, ничего про это не знаю. Что характерно, то что при записи макрос отрабатывает и подтягивает правильное значение, а при повторном нажатии уже ругается.
Формула конечно зверь) Если решили применять VBA, может имеет смысл расчеты перенести, чтоб не нагружать файл такими монстрами?
Я понимаю, что такая тяжелая формула затормозит расчет, но моя цель вставить ее строк на 50-100, ибо расчеты нужны для формирования понимания работы расчетов по базе для формирования ТЗ. Я даже пытался дать задание специалисту, но практически сразу понял, что на его конкретные вопросы мне ответить будет нечего, вот и решил хотя бы усеченно "прожить" формирование базы хотя бы в усеченной форме
Спасибо, я в экселе и тем более в VBA профан...Я сначала записал формулу в ячейку через рекодер, а потом просто скопировал получившийся вид... Вы хотите сказать, что при переносе в коду формула была деформирована и ее нужно восстановить? Я признаться, ничего про это не знаю. Что характерно, то что при записи макрос отрабатывает и подтягивает правильное значение, а при повторном нажатии уже ругается.
Формула конечно зверь) Если решили применять VBA, может имеет смысл расчеты перенести, чтоб не нагружать файл такими монстрами?
Я понимаю, что такая тяжелая формула затормозит расчет, но моя цель вставить ее строк на 50-100, ибо расчеты нужны для формирования понимания работы расчетов по базе для формирования ТЗ. Я даже пытался дать задание специалисту, но практически сразу понял, что на его конкретные вопросы мне ответить будет нечего, вот и решил хотя бы усеченно "прожить" формирование базы хотя бы в усеченной формеalex808
Вы хотите сказать, что при переносе в коду формула была деформирована и ее нужно восстановить
Или да, или деформировалась при копировании сюда на форум. Можно попробовать заменить в макросе все FormulaR1C1 на FormulaLocal. И Формулы скопируйте как есть (по-русски) не через запись рекодером.
Вы хотите сказать, что при переносе в коду формула была деформирована и ее нужно восстановить
Или да, или деформировалась при копировании сюда на форум. Можно попробовать заменить в макросе все FormulaR1C1 на FormulaLocal. И Формулы скопируйте как есть (по-русски) не через запись рекодером.sboy
Есть мнение, что деформация произошла при записи макрорекордером. Как-то он подозрительно (примерно так же) порезал у меня на строчки со съеданием нескольких концевых символов - видимо, подошёл к собственному пределу, но как-то корявенько...
Я попробовал другой путь - скопировал текст формулы на английском языке в OneNote, там подвигал границы и подобрал приемлемый размер текстового фрейма. В местах визуальных концов строки принудительно нажал Enter. Далее взял полученный текст в Блокнот, удвоил внутри текста все двойные кавычки при помощи "Найти и заменить". Затем добавил по двойной кавычке в конец каждой строки и в начало каждой строки вставил фрагмент: vb = vb & ". Получился следующий код для создания формулы:
Есть мнение, что деформация произошла при записи макрорекордером. Как-то он подозрительно (примерно так же) порезал у меня на строчки со съеданием нескольких концевых символов - видимо, подошёл к собственному пределу, но как-то корявенько...
Я попробовал другой путь - скопировал текст формулы на английском языке в OneNote, там подвигал границы и подобрал приемлемый размер текстового фрейма. В местах визуальных концов строки принудительно нажал Enter. Далее взял полученный текст в Блокнот, удвоил внутри текста все двойные кавычки при помощи "Найти и заменить". Затем добавил по двойной кавычке в конец каждой строки и в начало каждой строки вставил фрагмент: vb = vb & ". Получился следующий код для создания формулы:
Есть мнение, что деформация произошла при записи макрорекордером. Как-то он подозрительно (примерно так же) порезал у меня на строчки со съеданием нескольких концевых символов - видимо, подошёл к собственному пределу, но как-то корявенько...
Да именно так и произошло...Взял Ваш подход, чуток подправил для интервала, чтобы заполнил все ячейки в столбце и все заработало!!! Большое Вам и всем спасибо за подсказки и потраченное время!
Есть мнение, что деформация произошла при записи макрорекордером. Как-то он подозрительно (примерно так же) порезал у меня на строчки со съеданием нескольких концевых символов - видимо, подошёл к собственному пределу, но как-то корявенько...
Да именно так и произошло...Взял Ваш подход, чуток подправил для интервала, чтобы заполнил все ячейки в столбце и все заработало!!! Большое Вам и всем спасибо за подсказки и потраченное время!alex808