Перейти к содержимому
Наше приложение «Дешевые авиабилеты» в AppGallery >>

VBA Excel. Вставка формулы в ячейку

    Вставка формулы со ссылками в стиле A1 и R1C1 в ячейку (диапазон) из кода VBA Excel. Свойства Range.FormulaLocal и Range.FormulaR1C1Local.

    Свойство Range.FormulaLocal

    FormulaLocal — это свойство объекта Range, которое возвращает или задает формулу на языке пользователя, используя ссылки в стиле A1.

    В качестве примера будем использовать диапазон A1:E10, заполненный числами, которые необходимо сложить построчно и результат отобразить в столбце F:

    Примеры вставки формул суммирования в ячейку F1:

    Пример вставки формул суммирования со ссылками в стиле A1 в диапазон F1:F10:

    В этой статье я не рассматриваю свойство Range.Formula, но если вы решите его применить для вставки формулы в ячейку, используйте англоязычные функции, а в качестве разделителей аргументов — запятые (,) вместо точек с запятой (;):

    После вставки формула автоматически преобразуется в локальную (на языке пользователя).

    Свойство Range.FormulaR1C1Local

    FormulaR1C1Local — это свойство объекта Range, которое возвращает или задает формулу на языке пользователя, используя ссылки в стиле R1C1.

    Формулы со ссылками в стиле R1C1 можно вставлять в ячейки рабочей книги Excel, в которой по умолчанию установлены ссылки в стиле A1. Вставленные ссылки в стиле R1C1 будут автоматически преобразованы в ссылки в стиле A1.

    Примеры вставки формул суммирования со ссылками в стиле R1C1 в ячейку F1 (для той же таблицы):

    Пример вставки формул суммирования со ссылками в стиле R1C1 в диапазон F1:F10:

    Так как формулы с относительными ссылками и относительными по строкам ссылками в стиле R1C1 для всех ячеек столбца F одинаковы, их можно вставить сразу, без использования цикла, во весь диапазон.


    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    22 комментария для “VBA Excel. Вставка формулы в ячейку”

    1. Доброго времени суток.
      Кто-нибудь подскажет, как написать в vba excel вот такую формулу: ="пример текста " & D1 в ячейку, где «пример текста » и D1 должна быть выражена в виде переменных. В итоге в ячейке должно отобразиться: пример текста 50 при условии, что d1=50

      1. Евгений

        Привет, Nik!
        Записываем формулу в ячейку "A1", собрав ее из переменных:

        1. Спасибо большое! Совсем из вида упустил, что можно применить Chr(34).

    2. Ещё один вопрос, почему абсолютная ссылка получается ="Пример текста "&$D$1, как сделать, что бы была относительная ="Пример текста "&D1 ?

      1. Евгений

    3. Сусанна

      Здравствуйте. Помогите , пожалуйста. Возникает такая проблема: после замены части формулы с помощью функции Replace,значение в ячейке воспринимается как текст, а не как формула.
      Команды

      а также

      не помогли)). При этом, если подобную замену делать штатным экселевским Заменить, то полученный результат воспринимается как формула и вычисляется сразу.

      1. Евгений

        Здравствуйте, Сусанна!
        У меня работает так:

        1. Сусанна

          Огромное спасибо) В понедельник приду на работу, и обязательно попробую Ваш вариант.

    4. Дмитрий

      Добрый вечер. Мне нужно использовать математические операции, опираясь только на переменные. Например:
      Cells(i, SOH).Formula = (Cells(i, Stock_rep_date) + Cells(i, Consig_Stock_rep_date)) / 1
      Но в ячейках получаются сами значения, а нужна формула с ссылками на ячейки Cells.

      1. Евгений

        Здравствуйте, Дмитрий!
        Cells(i, SOH).Formula = "=(" & Cells(i, Stock_rep_date).Address & "+" & Cells(i, Consig_Stock_rep_date).Address & ")/1"

    5. Здравствуйте, Евгений!
      Можете помочь?
      Дано:
      1. В ячейках D1 и D2 некие текстовые данные, которые необходимо объединить в ячейку D3
      Range("D3").FormulaR1C1 = "=R[-2]C&R[-1]C"

      2. Потом в Ячейку D4 получившийся результат вставить как значение
      Range("D3").Copy
      Range("D4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

      3. И в ячейке D4 между данными вставить перенос на вторую строку. К примеру:
      Range("C4").FormulaR1C1 = "Видеокарта" & Chr(10) & "GTX 3090").

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

      Спасибо.

      1. Евгений

        Здравствуйте!
        Не совсем понял, что нужно, поэтому привожу пример, как объединить текст из двух ячеек (D1 и D2) в одну строку и как — в две:

        [D3] — это то же, что и Range("D3")

        1. Здравствуйте, Евгений!
          Это и нужно было.
          Огромное Вам спасибо!!!

    6. Дмитрий

      Добрый день! Подскажите в одном вопросе. Есть две ячейки, в одной из них находится число, при введении во вторую ячейку числа, число из первой ячейки уменьшается ровно на столько же, сколько введено во вторую ячейку. Очень важно, чтобы число в первой ячейки можно было хаотически менять, т.е. в первой ячейке была формула и число. Спасибо!

      1. Евгений

        Добрый день, Дмитрий!
        Ответ на ваш вопрос соответствует формуле =C1-B1, вставленной в ячейку A1. При вводе значения в ячейку B1, значение ячейки A1 уменьшается на значение ячейки B1. Изменяя значение ячейки C1, вы произвольно изменяете значение в ячейке A1.

        1. Дмитрий

          Не совсем то, что имел ввиду. Есть только две ячейки А1 и В1. В ячейке А1 должно быть две операции.
          1. Это вшитая формула.
          2. Это свободный ввод числа.
          Т.е. при внесении числа методом ввода или вставкой в ячейку А1 например 15, а в ячейку В1 например 4, значение в ячейке А1 автоматом менялось на 11. Можно конечно просто вносить в формулу ячейки А1=15-В1 и менять число 15 на любое другое, но это надо влезать в формулу. А нужен свободный ввод для вставки большого массива данных.

          1. Евгений

            Разместите следующий код в модуле листа:

    7. Дмитрий

      Евгений, спасибо большое!!! То, что надо, работает отлично!

    8. Диапазон С12:С27 может принимать одно из трех значений
      жар
      пар
      пир
      В столбце "Е" формула :
      =ЕСЛИ(C13="жар";СЛУЧМЕЖДУ(10;20);ЕСЛИ(C13="пир";ЦЕЛОЕ($F$10/10);ЕСЛИ(C13="пар";ЦЕЛОЕ($I$10/100);"")))
      меня попросили сделать решение на макросах
      For n = 12 To 27
      If Cells(n, 3) = "жар" Then Cells(n, 5).Value = Int(Rnd() * 10) + 11
      If Cells(n, 3) = "пир" Then Cells(n, 5).Value = Int(Range("f10") / 10)
      If Cells(n, 3) = "пар" Then Cells(n, 5).Value = Int(Range("i10") / 100)
      Next n

      всё замечательно работает.
      НО…
      по непонятной причине должна быть видна формула
      Range("Е13").FormulaLocal ="=ЕСЛИ(C13="жар";СЛУЧМЕЖДУ(10;20);ЕСЛИ(C13="пир";ЦЕЛОЕ($F$10/10);ЕСЛИ(C13="пар";ЦЕЛОЕ($I$10/100);"")))"
      машина ругается .
      новый подход:
      Range("С13:C27").FormulaR1C1 = ...
      и хрен не слаще редьки.
      Включаю и записываю макрорекордером . Машина формулу записывает на английском …
      Возможно ли : формулу скопировал и вставил в код макроса ?

    9. Евгений, здравствуйте. Имею только самые начальные представления в VBA. Хотелось бы выяснить в чем проблема в моем коде.
      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim strFormula As String
      If Target.Address = "$B$2" Then
      strFormula = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(R[-23]C[-2])&"":""&YEAR(R[-23]C[-1]))),12,31),LARGE(DATE(ROW(INDIRECT(YEAR(R[-23]C[-2])&"":""&YEAR(R[-23]C[-1]))),12,31),1),R[-23]C[-1])-(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(R[-23]C[-2])&"":""&YEAR(R[-23]C[-1]))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(R[-23]C[-2])&"":""&YEAR(R[-23]C[-1]))),1,1),1),R[-23]C[-2]+1))+1"
      Range("D26").FormulaLocal = strFormula
      Range("D27").Select
      End If
      End Sub

      Ошибку выдаёт на строке Range("D26").FormulaLocal = strFormula. Как возможно это исправить?
      С уважением Марина.

    Обсуждение закрыто.