Страницы: 1
RSS
Вставить формулу на лист средствами VBA, проблему вызвают адреса ячеек в формуле
 
Добрый день, уважаемые гуру! Прошу вашей помощи!

Есть таблица, по мере заполнения которой нужно вставлять из кода VBA в некоторые ячейки формулы.
особенность формул такова, что часть данных берется с текущего листа (PEE), а часть из другого (Данные)
Адреса ячеек в формулах то же надо как то менять, таблица то растет...
При выполнении процедуры возникает ошибка выполнения - мне кажется она связана с адресами ячеек.
Мне кажется нужно как то подменить адреса в формуле на переменные.. ну или я не знаю.. второй день лопачу интернет - похожего не вижу.
Прошу помощи!
использую Excel 2010

Код
Private Sub InsertFormula()
    With Sheets("PEE")
    .Cells(ActiveCell.Row, 4).Formula = "=ЕСЛИ(ЕОШИБКА(ВПР(B130;Данные!A:B;2;ЛОЖЬ));"";ВПР(B130;Данные!A:B;2;ЛОЖЬ))" 'для столбца  D
    .Cells(ActiveCell.Row, 8).Formula = "=ЕСЛИ(ЕОШИБКА(ВПР(D130;Данные!B:C;2;ЛОЖЬ));"";ВПР(D130;Данные!B:C;2;ЛОЖЬ))" 'для столбца H
    .Cells(ActiveCell.Row, 12).Formula = "ЕСЛИ(ЕОШИБКА(I130/(H130*0,82));"";I130/(H130*0,82))"                       'для столбца L
    End With
End Sub

Желаемый результат - вставка формулы в ячейку с учетом следующего правила: в функции ВПР адрес искомой ячейки - строка относительная столбец абсолютный
 
Если макросы разрешены - нужна ли формула?
 
поверьте - нужны! таблицу заполняют люди очень далекие от макросов.. в то же время для последующего анализа данные должны быть максимально унифицированы. Поэтому и возникло такое решение.
 
Вместо Formula - FormulaLocal

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

Хотя самый оптимальный и универсальный способ перенести формулу с листа в макрос - начать запись макроса, выделить ячейку с формулой, нажать F2, затем Enter. Остановить запись. Посмотреть, что записал рекордер.
Поверьте - и проще и всегда работать будет.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Код
.Cells(ActiveCell.Row, 4).FormulaR1C1 = "=ЕСЛИ(ЕОШИБКА(ВПР(RC2;Данные!C1:C2;2;ЛОЖЬ));"";ВПР(RC2;Данные!C1:C2;2;ЛОЖЬ))" 'для столбца  D

остальное аналогично.

пс. в Excel 2007-2013 можно использовать функцию ЕСЛИОШИБКА - и короче, и повторных вычислений не требует. но с 2003 и старше несовместима.

ппс. упс, на русские функции и кавычки я внимания не обратил.  :(  
The Prist прав.
тогда так:
Код
.Cells(ActiveCell.Row, 4).FormulaR1C1Local = "=ЕСЛИ(ЕОШИБКА(ВПР(RC2,Данные!C1:C2,2,ЛОЖЬ)),"""",ВПР(RC2,Данные!C1:C2,2,ЛОЖЬ))" 'для столбца  D
Изменено: ikki - 10.05.2013 19:02:43
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Саш, а ты тестил?
У меня так не работает.

Вариант
Код
Cells(ActiveCell.Row, 4).FormulaLocal = "=ЕСЛИ(ЕОШИБКА(ВПР(B130;Данные!A:B;2;ЛОЖЬ));"""";ВПР(B130;Данные!A:B;2;ЛОЖЬ))"
Изменено: LightZ - 10.05.2013 19:14:03
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
не тестил.
но с подачи Дмитрия я уже поправил точку с запятой на запятую.

потестил.
оказывается, для FormulaR1C1 нужны разделители запятые
а для FormulaR1C1Local - точка с запятой.
Изменено: ikki - 10.05.2013 19:25:09
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
LightZ, насколько я понял, автору темы нужна именно завязка на относительность строки.
а так, как у него и как у тебя - всегда берется строка с номером 130.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
А, точно :)
ну, тогда по идее нужно так (FormulaR1C1):
Код
Cells(ActiveCell.Row, 4).FormulaR1C1 = "=ЕСЛИ(ЕОШИБКА(ВПР(RC2,Данные!C1:C2,2,ЛОЖЬ)),"""",ВПР(RC2,Данные!C1:C2,2,ЛОЖЬ))"
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
у меня такой вариант дает ошибку #имя?
если FormulaR1C1, то надо использовать английские имена функций: IF, ISERROR, VLOOKUP
Изменено: ikki - 10.05.2013 19:32:36
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Вариант с FormulaR1C1Local - хоть убей, но ошибка vba 1004
Вариант с FormulaR1C1 - у меня работает нормально, нужно просто ЛОЖЬ заменить на 0 и повторно активировать формулу :)
С русскими названиями формул тоже работает, может это зависит от локали офиса?
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
при "повторно активировать" - ясен перец, заработает.
но, имхо, это неспортивно  :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Ок, давай тогда найдём универсальный вариант :)
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
Цитата
vikttur пишет:
Если макросы разрешены - нужна ли формула?
Цитата
андрей тетерин пишет:
поверьте - нужны! таблицу заполняют люди очень далекие от макросов.. в то же время для последующего анализа данные должны быть максимально унифицированы.
А этим людям что - нужно писать макросы? Они будут ими ПОЛЬЗОВАТЬСЯ. А Вы полагаете, что макросы не умеют работать с "максимально унифицированным данными"?
 
имхо, через FormulaR1C1 (с использованием английских функций) - лучше всего.
и от локали не зависит.
а то мало ли - попадёт такой макрос на англоязычный Excel - и пиши пропало  :)
Изменено: ikki - 10.05.2013 20:09:42
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
вот так работает:
Код
FormulaR1C1Local = "=ЕСЛИ(ЕОШИБКА(ВПР(RC2;Данные!C1:C2;2;ЛОЖЬ));"""";ВПР(RC2;Данные!C1:C2;2;ЛОЖЬ))"

и так:
Код
FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC2,Данные!C1:C2,2,FALSE)),"""",VLOOKUP(RC2,Данные!C1:C2,2,FALSE))"
Изменено: LightZ - 10.05.2013 20:43:06
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
Спасибо, уважаемые! низкий поклон LightZ, ikki, отдельное спасибо The_Prist, и vikttur, Вопрос решен. Тема закрыта
 
и да... вот рабочий код который сделан с помощь гуру. это для тех кто столкнется с таким вопросом:

Код
'Процедура вставки формул
Private Sub InsertFormula()
With Sheets("PEE")
        .Cells(ActiveCell.Row, 4).FormulaR1C1Local = "=ЕСЛИ(ЕОШИБКА(ВПР(RC2;Данные!C1:C2;2;ЛОЖЬ));"""";ВПР(RC2;Данные!C1:C2;2;ЛОЖЬ))" 'для столбца D
        .Cells(ActiveCell.Row, 8).FormulaR1C1Local = "=ЕСЛИ(ЕОШИБКА(ВПР(RC4;Данные!C2:C3;2;ЛОЖЬ));"""";ВПР(RC4;Данные!C2:C3;2;ЛОЖЬ))" 'для столбца Н
        .Cells(ActiveCell.Row, 12).FormulaR1C1Local = "=ЕСЛИ(ЕОШИБКА(RC9/(RC8*0,82));"""";RC9/(RC8*0,82))"                                                       'для столбца L
    End With
End Sub
 
Поясните пожалуйста. Вроде указываю диапазон для вставки.

Я делал раньше так
Код
Range(Cells(60,41).select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Лист3!R5C1:R496C23,7,0),0)"
   Selection.Copy
    Range(Cells(60, 41), Cells(410, 41)).Paste
Но хотелось бы более культурнее сделать код
 
DSH,
Код
Range(Cells(60, 41), Cells(410, 41)).FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Лист3!R5C1:R496C23,7,0),0)"
 
Казанский, Спасибо большое за помощь. Я понял суть.
Изменено: DSH - 10.04.2014 22:36:06
 
В следствии изменения предыдущего сообщения, смысл ответа утерян.
Изменено: RAN - 10.04.2014 23:40:00
Страницы: 1
Наверх