Текстовый аргумент в формуле макроса

[Dr_Lex]

Здравствуйте, столкнулся с такой закавыкой. Есть формула, которая в зависимости от имени клиента выбирает скидку и считает выручку. Формула выглядит так: "=ЕСЛИ(B13="Фирма1";B12*(1-'Вспомогательные расчёты'!B18);ЕСЛИ(B13="Фирма2";B12*(1-'Вспомогательные расчёты'!B19);ЕСЛИ(B13="Фирма3";B12*(1-'Вспомогательные расчёты'!B20);B12))). В ячейке экселя эта формула спокойно работает, там всё нормально. Но, есть редкие ситуации, когда эта формула не подходит и для ускорения процесса, сотрудник считает выручку вручную и просто вписывает значение в эту ячейку вместо формулы. Но для приёма следующего заказа, естественно, нужно, чтобы формула опять была в этой ячейке. Когда попытался заставить макрос вписывать в ячейку эту формулу, ему не понравился аргумент "Фирма1" (вторая и третья соответственно тоже). Код выглядит так:

Код Выделить Развернуть

    Range("B14").Select
    ActiveCell.Formula = "=ЕСЛИ(B13="Фирма1";B12*(1-'Вспомогательные расчёты'!B18); _
        ЕСЛИ(B13="Фирма2";B12*(1-'Вспомогательные расчёты'!B19); _
        ЕСЛИ(B13="Фирма3";B12*(1-'Вспомогательные расчёты'!B20);B12)))"
Пытался вместо .Formula использовать .FormulaLocal результат тот же.
Подскажите в чём может быть проблема?

[Serge 007]

Здравствуйте. Замените это:

Код Выделить Развернуть

    Range("B14").Select
    ActiveCell.Formula = "=ЕСЛИ(B13="Фирма1";B12*(1-'Вспомогательные расчёты'!B18); _
        ЕСЛИ(B13="Фирма2";B12*(1-'Вспомогательные расчёты'!B19); _
        ЕСЛИ(B13="Фирма3";B12*(1-'Вспомогательные расчёты'!B20);B12)))"

на это:

Код Выделить Развернуть

    [B14].FormulaR1C1 = _
        "=R[-2]C*(1-VLOOKUP(R[-1]C,'Вспомогательные расчёты'!R[4]C[-1]:R[6]C,2,))"

[Dr_Lex]

Спасибо за ответ. Но, к сожалению проблему он не решает. Пока клиентом является один из постоянных клиентов всё хорошо, но стоит ввести нового клиента результатом становится #Н/Д. Кроме того список я ввёл для удобства примера, по сути его в файле не будет. Грубо говоря есть три категории постоянных клиентов с различными условиями и их нельзя однозначно описать ссылками на ячейку со скидкой. Нужно, чтобы вставлялась формула Если(...) с текстовыми аргументами. Возможно ли это сделать?

[Serge 007]

...есть три категории постоянных клиентов с различными условиями и их нельзя однозначно описать ссылками на ячейку со скидкой. Нужно, чтобы вставлялась формула Если(...) с текстовыми аргументами.

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

PS Предложенная мной формула НИЧЕМ принципиально не отличается от Вашей, за исключением того, что мой вариант позволяет задать любое количество условий, а Ваша ограниченное (Excel 2003 - 7, Excel 2007 - 64, Excel 2010 и последующие - 128)

[Dr_Lex]

Не противоречу. Для простоты понимания задачи я задал трём разным фирмам три значения скидки 10, 15, 20 процентов. Но на деле это не три фирмы, это три категории и значение скидки не 10, 15, 20, а набор вычислений из многих параметров, которые, дабы не загромождать пример, я не включил в файл. В некоторых случаях это даже не скидка, а надбавка. Мне достаточно трёх условий и четырёх вариантов вычислений. Можно ли создать макрос, который будет записывать формулу в том виде, в котором она у меня сейчас в ячейке Excel? Если нельзя, тогда нужно будет подумать, как это сделать с помощью списка.

[vikttur]

Зачем записывать формулу? Можно в коде все посчитать и на лист выгрузить результат. Пересчет можно производить при изменении во влияющих ячейках.

[Dr_Lex]

Потому что в некоторых редких случаях, эти формулы не подходят и оператор будет туда число вписывать вручную, но к следующему заказу, нужно, чтобы формула снова была на месте. Кроме того, когда видна формула, оператору легче понять, что в данный момент происходит, а каждого нового (а меняются они довольно часто) обучать макросам (которые я сам почти не знаю) бесперспективно.
Люди, пожалуйста, нужно просто заставить макрос вставить такую формулу в ячейку. Возможно ли это?

[vikttur]

...каждого нового (а меняются они довольно часто) обучать макросам...

Т.е. обучить макросу, который вставляет формулу, можно, а другому - никак?
В данном случае специальных знаний не требуется. Нужно только знать, куда код вставить.
Смотрите пример. В столбце D сумма ячеек А и В текущей строки. Не нужно вставлять формулу, которую потом могут затереть.

[Serge 007]

Не противоречу

Противоречите. И я объяснил Вам, в чем именно. Печально что Вы этого не понимаете

...набор вычислений из многих параметров, которые, дабы не загромождать пример, я не включил в файл...

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

...Можно ли создать макрос, который будет записывать формулу в том виде, в котором она у меня сейчас в ячейке Excel?

Можно. Решение вставлять Вашу формулу макросом самое нелепое из всех существующих, однако раз Вы нормальные варианты не приемлете, то...

Код Выделить Развернуть

Range("B5").FormulaR1C1 = _
        "=IF(R[-1]C=""Фирма1"",R[-2]C*(1-'Вспомогательные расчёты'!R[4]C),IF(R[-1]C=""Фирма2"",R[-2]C*(1-'Вспомогательные расчёты'!R[5]C),IF(R[-1]C=""Фирма3"",R[-2]C*(1-'Вспомогательные расчёты'!R[6]C),R[-2]C)))"

[Dr_Lex]

Т.е. обучить макросу, который вставляет формулу, можно, а другому - никак?

Я и не обучаю других макросам, у оператора только кнопка и всё, макросом занимаюсь я. Но формулу на листе он видеть будет, а макрос нет. И как раз для того чтобы формула не затёрлась нужен макрос, который её каждый раз возвращает на место.
В вашем примере всё работает до тех пор, пока не нужно затереть значение суммы ручным значением. После этого даже изменение значений в столбцах A и B не приводит к правильному расчёту. Остаётся ручное значение. Оно поменяется лишь в том случае, если я в эти столбцы задам число вручную, а у меня везде формулы.

[Dr_Lex]

Вот их-то и надо было в файле выложить.

Это огромное количество параметров, в которых, чтобы понять, что мне нужно, вам потребовалось бы ещё разбираться и тратить своё время. Я максимально упростил пример, чтобы не тратить ваше время. Вы же не будете за меня каждый раз выполнять мою работу. Значит мне нужно разобраться самому, как и что делать, если похожая ситуация возникнет вновь, чтобы не дёргать людей по одному и тому же поводу сотню раз. Макросы для меня тёмный лес (пока), соответственно я использую их лишь в меру моих скромных возможностей, стараясь решать все поставленные задачи на рабочем листе Excel через формулы. Я не смог бы сам автоматизировать расчёт макросами, а просить это сделать других людей... ну это совсем обнаглеть. Расчёт в достаточной степени автоматизирован с помощью формул, они тоже дают неплохое пространство для манёвров.

Решение вставлять Вашу формулу макросом самое нелепое из всех существующих, однако раз Вы нормальные варианты не приемлете, то...

Простите, что я такой упёртый баран но для меня это лучшее решение, которое я смогу понять и воплощать в дальнейшем. Как говорится, самая короткая тропинка та, которую ты знаешь. Огромное спасибо за помощь. Всё работает, оказывается нужно было лишь удвоить кавычки.

[IKor]

На всякий случай добавлю свои 5 копеек:
в тех случаях когда в моих таблицах появляется риск "порчи" пользователями формул, я стараюсь модифицировать формулы таким образом, чтобы рядом (в том же столбце или строке) находилась формула, которую можно скопировать вместо испорченной. Обычно такие ячейки я выделяю одинаковым цветом фона: и в большинстве случаев пользователям даже не приходится давать дополнительных пояснений...

[ShAM]

Ну, и мои 5 коп. Если хотите, чтобы было как у Вас, т.е. по-русски, то добавьте Local:

Код Выделить Развернуть

ActiveCell.FormulaLocal = _
"=ЕСЛИ(B13=""Фирма1"";B12*(1-'Вспомогательные расчёты'!B18);ЕСЛИ(B13=""Фирма2"";B12*(1-'Вспомогательные расчёты'!B19);ЕСЛИ(B13=""Фирма3"";B12*(1-'Вспомогательные расчёты'!B20);B12)))"
ЗЫ: Удвоение кавычек, конечно, тоже нужно.