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

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

Содержание

1. Свойство Range.FormulaLocal

2. Свойство Range.FormulaR1C1Local

Свойство Range.FormulaLocal

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

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

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

1 2	Range("F1").FormulaLocal = "=СУММ(A1:E1)" Range("F1").FormulaLocal = "=СУММ(A1;B1;C1;D1;E1)"

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

Sub Primer1()

Dim i As Byte

For i = 1 To 10

Range("F" & i).FormulaLocal = "=СУММ(A" & i & ":E" & i & ")"

End Sub

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

1	Range("F1").Formula = "=SUM(A1,B1,C1,D1,E1)"

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

Свойство Range.FormulaR1C1Local

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

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

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

'Абсолютные ссылки в стиле R1C1:

Range("F1").FormulaR1C1Local = "=СУММ(R1C1:R1C5)"

Range("F1").FormulaR1C1Local = "=СУММ(R1C1;R1C2;R1C3;R1C4;R1C5)"

'Ссылки в стиле R1C1, абсолютные по столбцам и относительные по строкам:

Range("F1").FormulaR1C1Local = "=СУММ(RC1:RC5)"

Range("F1").FormulaR1C1Local = "=СУММ(RC1;RC2;RC3;RC4;RC5)"

'Относительные ссылки в стиле R1C1:

Range("F1").FormulaR1C1Local = "=СУММ(RC[-5]:RC[-1])"

Range("F2").FormulaR1C1Local = "=СУММ(RC[-5];RC[-4];RC[-3];RC[-2];RC[-1])"

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

'Ссылки в стиле R1C1, абсолютные по столбцам и относительные по строкам:

Range("F1:F10").FormulaR1C1Local = "=СУММ(RC1:RC5)"

'Относительные ссылки в стиле R1C1:

Range("F1:F10").FormulaR1C1Local = "=СУММ(RC[-5]:RC[-1])"

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

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

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

Nik 29.08.2022 в 20:02

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

Евгений 29.08.2022 в 20:46

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

Sub Test()

Dim s As String, myRange As Range

s = "пример текста "

Range("D1") = 50

Set myRange = Range("D1")

Range("A1").FormulaLocal = "=" & Chr(34) & s & Chr(34) & Chr(38) & myRange.Address

End Sub

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

Nik 29.08.2022 в 21:17

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

Евгений 29.08.2022 в 23:31

Sub Test1()

Dim s As String, r As String

s = "пример текста "

r = "D1"

Range(r) = 50

Range("A1").FormulaLocal = "=" & Chr(34) & s & Chr(34) & Chr(38) & r

End Sub

1. Nik 30.08.2022 в 09:05
  
  Ещё раз большое спасибо за оперативность.

Сусанна 16.09.2022 в 08:37

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

1
2
3

Cells(a,d).Formula=Cells(a,d).Value
Cells(a,d).FormulaLocal=Cells(a,d).Value
Cells(a,d).FormulaR1C1Local=Cells(a,d).Value

а также

1

Cells(a,d).Value=Evaluate(Ref)

не помогли)). При этом, если подобную замену делать штатным экселевским Заменить, то полученный результат воспринимается как формула и вычисляется сразу.
1. Евгений 16.09.2022 в 17:32
  
  Здравствуйте, Сусанна!
  У меня работает так:
  
  1
  
  Range("A10").FormulaLocal = Replace(Range("A10").FormulaLocal, "H3", "H10")
2. 1. Сусанна 17.09.2022 в 04:32
    
    Огромное спасибо) В понедельник приду на работу, и обязательно попробую Ваш вариант.
Дмитрий 14.10.2022 в 16:21

Добрый вечер. Мне нужно использовать математические операции, опираясь только на переменные. Например:
Cells(i, SOH).Formula = (Cells(i, Stock_rep_date) + Cells(i, Consig_Stock_rep_date)) / 1
Но в ячейках получаются сами значения, а нужна формула с ссылками на ячейки Cells.
1. Евгений 14.10.2022 в 23:25
  
  Здравствуйте, Дмитрий!
  Cells(i, SOH).Formula = "=(" & Cells(i, Stock_rep_date).Address & "+" & Cells(i, Consig_Stock_rep_date).Address & ")/1"
Nickvest 25.12.2022 в 08:59

Здравствуйте, Евгений!
Можете помочь?
Дано:
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").

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

Спасибо.

Евгений 25.12.2022 в 19:45

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

Sub Test()

'в D3 объединяем в одну строку

[D3] = [D1] & [D2]

'в D4 объединяем в две строки

[D4] = [D1] & vbNewLine & [D2]

End Sub

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

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

Дмитрий 11.01.2023 в 12:23

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

Евгений 11.01.2023 в 15:41

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

Дмитрий 12.01.2023 в 04:55

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

Евгений 12.01.2023 в 17:54

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

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Cells(1, 2), Range(Target.Address)) Is Nothing _

And IsNumeric(Cells(1, 2)) Then

Cells(1, 1) = Cells(1, 1) - CVar(Cells(1, 2))

End If

End Sub

Дмитрий 13.01.2023 в 05:46

Евгений, спасибо большое!!! То, что надо, работает отлично!
0mega 30.04.2023 в 15:05

Диапазон С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 = ...
и хрен не слаще редьки.
Включаю и записываю макрорекордером . Машина формулу записывает на английском …
Возможно ли : формулу скопировал и вставил в код макроса ?
Марина 18.05.2023 в 19:18

Евгений, здравствуйте. Имею только самые начальные представления в 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. Как возможно это исправить?
С уважением Марина.
Марина 18.05.2023 в 19:59

Спасибо, проблема решена.

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