Excel автоматически добавляет дополнительную строку с формулами для введенного количества лет

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

Чтобы быть более конкретным, у меня есть таблица, полная формул, которая имеет ряд для каждого года, начиная с 2005-2014 годов.
Мой запрос выглядит следующим образом:

Есть ли способ сделать страницу ввода, которая занимает годы, и изменить таблицу и добавить / вычесть строки на основе введенного диапазона? Например, мы меняем диапазон на странице ввода на 2005-2018 гг., Тогда моя таблица добавит еще 4 строки с уравнениями.

Решение
Чтобы автоматизировать это, используйте некоторый код VBA, который изменяет размер вашей таблицы после обновления начального и конечного лет.

Код
Вот моя реализация этого, с годами в «Sheet1» (ячейки B1 и B2) и таблицы в «Sheet2». Обратите внимание, что вы должны добавить этот код на лист, в котором находятся годы, потому что код должен запускаться всякий раз, когда вы что-то изменяете на этом листе. Поэтому в моем случае этот код добавляется в «Sheet1».

Private Sub Worksheet_change(ByVal Target As Range) Dim StartYear, EndYour As Range Dim UpdateTable As ListObject Dim NrOfRows, OldNrOfRows As Integer ' Set some ranges Set StartYear = Worksheets("Sheet1").Cells(1, 2) Set EndYear = Worksheets("Sheet1").Cells(2, 2) Set UpdateTable = Worksheets("Sheet2").ListObjects("Table1") ' Check if start or end years have changed If (Not Intersect(StartYear, Target) Is Nothing) Or (Not Intersect(EndYear, Target) Is Nothing) Then ' Store the new and old number of rows OldNrOfRows = UpdateTable.ListRows.Count - 1 NrOfRows = EndYear.Value - StartYear.Value + 1 ' Resize the table UpdateTable.Resize UpdateTable.Range.Resize(1 + NrOfRows) 'Delete cells below the table if it gets smaller If OldNrOfRows > NrOfRows Then UpdateTable.Range.Offset(NrOfRows + 1, 0).Resize(OldNrOfRows - NrOfRows + 1).Delete End If End If End Sub 

Некоторые моменты объяснения кода
Некоторые ссылки на функции / методы / свойства Microsoft для объяснения кода:

  • Excel может вызывать sub, когда изменяется значение ячейки . Этот sub называется Worksheet_change а ячейка, которая была изменена, задается как значение диапазона.
  • Я использую функцию Intersect, чтобы проверить, изменились ли ячейки, которые содержат начало / конец года. Конструкция NOT-NOTHING может сделать ее более трудной для чтения, но эффект заключается в том, что IF вводится, если NOT-NOTHING изменен.
  • Чтобы изменить размер таблицы, я использую метод Resize объекта ListObject (для которого требуется объект диапазона) и метод изменения размера объекта диапазона (для которого требуется один или два int).
  • Наконец, если таблица становится меньше, мне пришлось вручную удалить содержимое ниже новой таблицы. Здесь я снова использую некоторые функции изменения смещения диапазона и диапазона, чтобы удалить правильный диапазон .
  • Проблемы с макросом веб-запросов
  • Как игнорировать первую строку в макросе Excel, которая удаляет все строки на основе критериев?
  • Как использовать workbook.saveas с автоматической перезаписью
  • Добавление элементов в список с несколькими столбцами
  • Сохранить текстовый файл UTF-8, закодированный с помощью VBA
  • Формула разделения в excel
  • Сохранение нескольких листов в .pdf
  • Запуск макроса Excel из внешнего Excel с помощью VBScript из командной строки
  • Разбор нескольких ячеек и значений из одного запроса JSON
  • Excel VBA Если WorkSheet («wsName») существует
  • Как я могу base64 эффективно кодировать строку с помощью Excel VBA?
  • Давайте будем гением компьютера.