Как сместить формулы в VBA при добавлении новых строк в Excel

@bsi80 · Регистрация: 29.05.2012

Студворк — интернет-сервис помощи студентам

Имеется очень много формул в таблице, что бы избежать их нечаянного удаления или при добавлении новых строк и столбцов, заполнить их формулами, хочу вывести на кнопку пересчет всех формул в диапозоне. Но при добавлении новых строк формула не меняет свой диапозон, а остаётся фиксированной. Как сделать что-бы формула в VBA была динамической и перемещался бы диапозон сумирования исходя из того что кто то добавил новую строку сверху.
вот макрос которым проставляю формулы

Visual Basic
1
2
3
4
5
Sub now()
With Range("a10:d10")
     .Formula = "=sum(A11:A20)"
End With
End Sub

@Казанский · 11.07.2012, 09:52

Сверху чего? Строки с формулой или диапазона суммирования?
Может, надо определить последнюю строку, занятую данными? Это можно сделать так:

Visual Basic
1
2
3
4
5
6
7
Sub Sums()
Dim lastRow&
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("a10:d10")
    .Formula = "=sum(A11:A" & lastRow & ")"
End With
End Sub

И переименуйте процедуру, а то не сможете использовать одноименную функцию Now().

@bsi80 · 11.07.2012, 10:22 **[ТС]**

Сообщение от Казанский

Сверху чего? Строки с формулой или диапазона суммирования?
Может, надо определить последнюю строку, занятую данными? Это можно сделать так:

Visual Basic
1
2
3
4
5
6
7
Sub Sums()
Dim lastRow&
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("a10:d10")
    .Formula = "=sum(A11:A" & lastRow & ")"
End With
End Sub

И переименуйте процедуру, а то не сможете использовать одноименную функцию Now().

Если в Excel добавлять строки сверху, соответственно весь диапазон смещается с формулой и диапазон суммирования. А в макросе формулы (в моем случае) написаны как фиксированные. Для строки с формулой можно присвоить имя, а вот диапазон суммирования должен быть динамический. В Вашем случае макрос определяет последнею строку по первой столбцу "a10" и далее в "в10" "с10" "d10" ставит такие же формулы, но если в этих столбцах "в10" "с10" "d10" есть значения для суммирования ниже столбца "a10" то они не суммируются. Как бы выйти из такого положения?

@Казанский · 11.07.2012, 10:56

Сообщение от bsi80

Для строки с формулой можно присвоить имя

Хорошая мысль. Вот так можно в каждой ячейке именованного диапазона просуммировать от ячейки ниже до строки с последней ячейкой:

Visual Basic
1
2
3
4
5
6
7
Sub sums()
Dim lastRow&
lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
With Range("суммы") 'имя диапазона
    .FormulaR1C1 = "=sum(R[1]C:R" & lastRow & "C)"
End With
End Sub

@bsi80 · 11.07.2012, 11:07 **[ТС]**

см.в примере. Так наглядно будет понятно, что получается и как хотелось бы видеть.

@Busine2012 · 11.07.2012, 15:59

bsi80, т.е. в выложенном файле в ячейке E4 должна быть сумма по диапазону E5:E9? А у вас сейчас считается сумма по диапазону E5:E16. Так?

Т.е. ваша задача состоит в определении последней ячейки в разделе Стадия рабочая документация (это название раздела в ячейке A4)?

@bsi80 · 11.07.2012, 16:50 **[ТС]**

Сообщение от Busine2012

bsi80, т.е. в выложенном файле в ячейке E4 должна быть сумма по диапазону E5:E9? А у вас сейчас считается сумма по диапазону E5:E16. Так?

Т.е. ваша задача состоит в определении последней ячейки в разделе Стадия рабочая документация (это название раздела в ячейке A4)?

Да всё правильно поняли.

@Busine2012 · 11.07.2012, 19:49

bsi80, тогда на основе вашей книги нужно ориентироваться на какой-то текст.
Можно ли ориентироваться на текст в ячейке A10: Дополнительные проектные работы? Или этого текста может и не быть?

@bsi80 · 11.07.2012, 20:07 **[ТС]**

Сообщение от Busine2012

bsi80, тогда на основе вашей книги нужно ориентироваться на какой-то текст.
Можно ли ориентироваться на текст в ячейке A10: Дополнительные проектные работы? Или этого текста может и не быть?

Да можно ориентироваться. Это заглавные строки которые меняться не будут.

@Busine2012 · 11.07.2012, 20:29

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Procedure_1()
 
    'Здесь нужно укзать название раздела, который находится
    'ниже нужного раздела.
    Const sРаздел2 As String = "Дополнительные проектные работы"
    
    Dim lLastRow As Long
    
    'Находим название следующего раздела и определяем, строку, в которой он находится.
    'Т.к. нас интересует предыдущая строка, то отнимаем число 1.
    lLastRow = ActiveSheet.Columns("A").Find(What:=sРаздел2, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row - 1
 
    Range("Стадия_раб_док").Formula = "=SUM(E5:E" & lLastRow & ")"
 
End Sub

@bsi80 · 11.07.2012, 21:04 **[ТС]**

Busine2012, Да так получается, но если я добавляю строки сверху "Стадия рабочая документация" то суммирует диапозон с ячейки E5, как тут быть. Поясню зачем добавляются строки, т.к работ много и они постоянно добавляютя, необходимо их вносить, каждую работу в свой раздел и эти разделы должны быть так сказать мобильными. С нижней строкой уже всё получается, осталось придумать как быть с верхней строкой.

@Busine2012 · 12.07.2012, 08:32

bsi80, надо находить в столбце A заголовок раздела и определять номер строки, где находится название раздела. Затем формулу нужно так записать:

Visual Basic
1
Range("Стадия_раб_док").Formula = "=SUM(E" & lFirstRow & ":E" & lLastRow & ")"

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Основы отладки веб-приложений на SDL3 по USB и Wi-Fi, запущенных в браузере мобильных устройств 8Observer8 07.02.2026 Содержание блога Браузер Chrome имеет средства для отладки мобильных веб-приложений по USB. В этой пошаговой инструкции ограничимся работой с консолью. Вывод в консоль - это часть процесса. . .	SDL3 для Web (WebAssembly): Обработчик клика мыши в браузере ПК и касания экрана в браузере на мобильном устройстве 8Observer8 02.02.2026 Содержание блога Для начала пошагово создадим рабочий пример для подготовки к экспериментам в браузере ПК и в браузере мобильного устройства. Потом напишем обработчик клика мыши и обработчик. . .	Философия технологии iceja 01.02.2026 На мой взгляд у человека в технических проектах остается роль генерального директора. Все остальное нейронки делают уже лучше человека. Они не могут нести предпринимательские риски, не могут. . .	SDL3 для Web (WebAssembly): Вывод текста со шрифтом TTF с помощью SDL3_ttf 8Observer8 01.02.2026 Содержание блога В этой пошаговой инструкции создадим с нуля веб-приложение, которое выводит текст в окне браузера. Запустим на Android на локальном сервере. Загрузим Release на бесплатный. . .
SDL3 для Web (WebAssembly): Сборка C/C++ проекта из консоли 8Observer8 30.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .	SDL3 для Web (WebAssembly): Установка Emscripten SDK (emsdk) и CMake для сборки C и C++ приложений в Wasm 8Observer8 30.01.2026 Содержание блога Для того чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. . . .	SDL3 для Android: Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .	Инструменты COM: Сохранение данный из VARIANT в файл и загрузка из файла в VARIANT bedvit 28.01.2026 Сохранение базовых типов COM и массивов (одномерных или двухмерных) любой вложенности (деревья) в файл, с возможностью выбора алгоритмов сжатия и шифрования. Часть библиотеки BedvitCOM Использованы. . .

@Busine2012 1302 / 404 / 22 Регистрация: 21.10.2011 Сообщений: 1,285
	11.07.2012, 15:59
	bsi80, т.е. в выложенном файле в ячейке E4 должна быть сумма по диапазону E5:E9? А у вас сейчас считается сумма по диапазону E5:E16. Так? Т.е. ваша задача состоит в определении последней ячейки в разделе Стадия рабочая документация (это название раздела в ячейке A4)? 0

@Busine2012 1302 / 404 / 22 Регистрация: 21.10.2011 Сообщений: 1,285
	11.07.2012, 19:49
	bsi80, тогда на основе вашей книги нужно ориентироваться на какой-то текст. Можно ли ориентироваться на текст в ячейке A10: Дополнительные проектные работы? Или этого текста может и не быть? 0

@bsi80 0 / 0 / 0 Регистрация: 29.05.2012 Сообщений: 14
	11.07.2012, 21:04 [ТС]
	Busine2012, Да так получается, но если я добавляю строки сверху "Стадия рабочая документация" то суммирует диапозон с ячейки E5, как тут быть. Поясню зачем добавляются строки, т.к работ много и они постоянно добавляютя, необходимо их вносить, каждую работу в свой раздел и эти разделы должны быть так сказать мобильными. С нижней строкой уже всё получается, осталось придумать как быть с верхней строкой. 0